[Solved] Text box updated from data entered into a listbox

Creating and using forms
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Text box updated from data entered into a listbox

Post by Maax555 »

Hi All, I am slowly working through a database I am creating. I have the simple stuff working, add a record with the various fields. However in the form i wish to have a field that is a similar part to that of which I am currently entering.
I have the list box working which lists all the partnumbers currently in the database. However I wish to populate the form with further information relating to the similar partnumber i have entered.

So in the attached shot I enter new partnumber etc,as I work through the fields I then choose from the REPLACES listbox the product which this new partnumber is to replace or is similar too. I then wish for the 7 text boxes to the right to automatically populate with the data already saved in the PARTNUMBER TABLE.

The 7 text boxes do not need to store any data they just need to populate based on the REPLACES listbox entry for reference at time of form entry.

I PartNumber, Customer, Employee, Complexity and CatType tables. The 4 tables being used so i can have a listbox in the Partnumber form.

I currently have no relationships in the database.

I have downloaded a few examples to try to work this out but have struggled so far as wish to keep this as simple as possible.

Hope this makes some kind of sense?

Any help appreciated.
Attachments
base1.JPG
Last edited by Hagar Delest on Sun May 28, 2017 9:27 pm, edited 1 time in total.
Reason: tagged [Solved].
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Text box updated from data entered into a listbox

Post by UnklDonald418 »

Without more details about your tables I can only guess but ...
To automatically populate the details boxes for Replacing they will need to appear on a SubForm. Likewise Similar details on another SubForm.
But the box for Replaces and also Similar will probably be on the MainForm. I'm assuming there is a field for each in the PartNumber table.
Each SubForm will likely need a query to supply the data and will need to be linked to the MainForm probably using the PartNumber found in the respective Replaces and Similar boxes.

Is there always only one Replaces or can there be more than one and likewise with Similar?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

I would only enter one partnumber in the replaces and similar boxes. These would already exist in the partnumber table.

My replaces and similar listbox's work fine. They list all the previous entries in the database, i then need the boxes to the right to bring back the wire numbers that match the partnumber entered into the replacing list box.

I have achieved this in MS Access many years ago so it must have been reasonably simple for me to manage. However I had a lot of help and cannot recall how I did it.

If you are aware of any example databases that have this function in so i could see how it is achieved I would prefer to do it that way.

thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Text box updated from data entered into a listbox

Post by UnklDonald418 »

I uploaded a simple demonstration with 2 subforms, Replaces and Similar.
Attachments
Demo07.odb
(13.78 KiB) Downloaded 560 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Thanks Donanld, this is not working for me. The price field etc does not update when i change the replaces item. I will try adding a refresh form button. However there is an icon advising that there is an update to libreoffice so I will update and then try again. Thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Ahh, the refresh button did the trick. I will now play with your example and try have the fields update on there own without the need for a button, hopefully I can manage this. Although when i press the refresh button i get a pop up asking if i wan to save the entry. I would prefer the form to auto refresh and only save when i press a save button at end. Finally before I build this into my form I need to have the form open at the next available free record as currently opening at the first record.

thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Text box updated from data entered into a listbox

Post by UnklDonald418 »

have the fields update on there own without the need for a button
The following macro should do that when invoked by the After updating event for the Replaces list box.

Code: Select all

Sub RefreshForm(oEvent as object)

 oEvent.Source.Parent.getByName("SubFormReplaces").reload

End Sub    'Refresh Form
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Hi Donald, its been a while since I have been able to return to this project. The above macro works but only if I click in one of the fields which will update after choosing the replaces part from the dropdown.

So for example i choose the replaces value but then have to click in the replaces price field for the form to update with the matching information.

Is there an easy fix so the update is immediate after choosing the replaces value?

many thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text box updated from data entered into a listbox

Post by Villeroy »

viewtopic.php?f=21&p=418049#p418049 for whatever you need to refresh after you actually saved a record. Refreshing and reloading things to reflect the latest changes can only work when things have been stored in the database. Reacting on data that have not been saved yet requires a more complicated program.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Thanks Villeroy, in that case is it possible to have a report within a form? I could then enter a similar part number and the details for that would be shown in the form?
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Dear all, i am attaching my file for your amusement at my lack of DB skills :-)

The aim of this DB is for the user to enter details for a new part and at the same time list the part that the new part will replace and also list a part that it is similar too.
This will then allow the user to compare the previous details against new details.

so in the replacing box the user chooses from a drop down list the part number to be replaced. The details to the right will then be populated from the main table as this part will have previously been entered into the DB.

The same is true of the similar drop down.

After considering all things I have no problem if i have to have a refresh button for the replacing and similar details.
So choose replacing part number from drop down and press update replacing part button for the detail on the right to be populated and again same for similar.

I would also like to have the replacing and similar part numbers saved with the record so the next time it is viewed or opened the similar and replacing part numbers are already present.

I have other details to add such as a few calculations but help on the above would be massively appreciated.

many thanks
Attachments
Project Tracking test.odb
(91.46 KiB) Downloaded 395 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: Text box updated from data entered into a listbox

Post by chrisb »

Maax555,
this applies to the attachment 'Demo07.odb' as uploaded by forum user UnklDonald418:-
when using the form & selecting a new value in the list box 'Replaces' or the list box 'Similar' then the code below will update the table & reload the relevant subform.
it should be applied to the list boxes 'txtReplaces' & 'lbxSimilar' & seems to work best when using the event 'Item status changed'.

Code: Select all

sub ListBoxChanged (oEv as object)
	dim oForm as object, oControl as object, sSubform as string

	oControl = oEv.source.model
	oForm = oControl.parent

	oControl.commit()
	oForm.updaterow

	if oControl.name = "txtReplaces" then
		sSubform = "SubFormReplaces"
	else
		sSubform = "SubFormSimilar"
	endif
	
	oForm.getbyname(sSubform).reload
end sub
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Hi Chrisb, brilliant. Works instantly on the DEMO07 DB, I will now try to incorporate this into my project database.

many thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: Text box updated from data entered into a listbox

Post by chrisb »

Maax555, please note:-

1. if the text box 'item' is empty (i.e. new record) then the macro will throw an error. insert this code directly below the line which reads 'oForm = oControl.parent' in order to address the issue.

Code: Select all

if oForm.getbyname("txtItem").currentvalue = "" then exit sub 'if Item is empty then exit
2. when we use the event 'Item status changed' to fire the macro then it will repeatedly execute as we scroll through the list entries using the mouse wheel.
using properties/General it may be wise to set 'Mouse wheel scroll' = 'Never'.
alternatively assign to the event 'Changed', it's noticeably slower but avoids any possible issues.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Thanks Chrisb, I am however getting this error with or without the additional line in my project DB.
Attachments
error1.JPG
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: Text box updated from data entered into a listbox

Post by chrisb »

Maax555,
did you assign the macro to the two appropriate list box controls.
did you check that the two control and two subform names (red text within double quotes) accurately reflect the names of your database objects.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Hi Chrisb, I believe the Macro and above suggestion were entered correctly. I have removed the macro for now as I have a bigger issue whereby i getting error on inserting new record. I need to try fathom this one out first :-(
Attachments
error2.JPG
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
chrisb
Posts: 294
Joined: Mon Jun 07, 2010 4:16 pm

Re: Text box updated from data entered into a listbox

Post by chrisb »

Maax555,
the attachment you uploaded contains several basic errors.
the field 'ID' which is the primary key in the table "PartNumber" was not set to auto value.
the form "PartNumber" contains a form named "Mainform" which had the property 'Add data only' set to yes.
one of your subforms is called "SubformReplacing" & not "SubformReplaces" as contained in the macro.
i have made the minimum number of alterations required to enable the form to function & updated the macro slightly.
Project Tracking_1.odb
(90.75 KiB) Downloaded 390 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Hi chrisb, yes i spotted and fixed the ID key which made a big difference :-)
I also corrected the subform naming error.
I tinkered with the "add data only" as I was trying to get the form to open at the next empty record for adding. It was starting at record 1.
I have fixed a few other things so will download and open your modified version to compare to what I have now.

Many thanks for your help as its really appreciated.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

OK, starting to look good lol.
However the modified one i downloaded was coming up with errors so I have used my previous one and tweaked.

Currently I have two issues,

1) If i go to a previously entered record and change the replaces drop-down list to another value I get an error.
2) When i change the similar list the details to the right do not automatically refresh and update. They do if i save the record and then go back to it.

I would like the replaces and similar details to update as soon at the new item is chosen from the drop down list.
Failing that I don't mind having a refresh button but would prefer to do with out.

thanks again.
Attachments
Project Tracking test.odb
(113.31 KiB) Downloaded 364 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

I see i have missed the macro from the similar drop down list so thats why its not trying to update.
However I get same error with both drop downs after selecting the item number.

The error is " oForm.getbyname(sSubform).reload" I will investigate further
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Text box updated from data entered into a listbox

Post by Maax555 »

Success, didnt realise strings are case sensitive.

many thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: [Solved] Text box updated from data entered into a listb

Post by Maax555 »

Guys, I am back. After a lengthy break i have returned to this database creation. Unfortunately i feel like i have forgotten a lot of what i had only just learned.
I am still on the same problem which is the updating of the replacing details and similar details fields.

I have again attached my database for any kind soul up for a challenge.

So if we open the database, go to forms and open PartNumber the form opens at record 1 with all details filled in and correct (for now).
If we then click in the replaces or similar dropdown and choose another part number, the values to the right instantly update.

However when adding a new record I get a function sequence error. I obviously need to fix this but struggling somewhat.
On the same issue of updating the replaces and similar dropdown lists, i want the part numbers in the dropdown list to be restricted to part numbers that belong to the customer I have already updated at the top of the form.

As usual if anyone can help with this I would appreciate it so i can move forward a little.

Many thanks Russ
Attachments
Project Tracking test.odb
(116.32 KiB) Downloaded 339 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Text box updated from data entered into a listb

Post by UnklDonald418 »

The function sequence error is in your macro code is caused by trying to update a row that doesn't exist.
Add the following somewhere before oControl.commit()

Code: Select all

   if oForm.isNew then
      oForm.insertRow
   end if
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: [Solved] Text box updated from data entered into a listb

Post by Maax555 »

Many thanks Unk, works a treat. I will re-post the other part of the related problem in a new post so i can close this one.

Much appreciated.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply