Page 1 of 1

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

Posted: Tue Mar 28, 2017 12:17 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Tue Mar 28, 2017 11:32 pm
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?

Re: Text box updated from data entered into a listbox

Posted: Wed Mar 29, 2017 10:52 am
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

Re: Text box updated from data entered into a listbox

Posted: Wed Mar 29, 2017 6:00 pm
by UnklDonald418
I uploaded a simple demonstration with 2 subforms, Replaces and Similar.

Re: Text box updated from data entered into a listbox

Posted: Thu Mar 30, 2017 9:15 am
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

Re: Text box updated from data entered into a listbox

Posted: Thu Mar 30, 2017 9:29 am
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.

Re: Text box updated from data entered into a listbox

Posted: Thu Mar 30, 2017 4:48 pm
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

Re: Text box updated from data entered into a listbox

Posted: Tue May 23, 2017 10:35 am
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

Re: Text box updated from data entered into a listbox

Posted: Tue May 23, 2017 12:55 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 10:04 am
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?

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 10:44 am
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

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 1:18 pm
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

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 2:27 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 3:47 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 4:39 pm
by Maax555
Thanks Chrisb, I am however getting this error with or without the additional line in my project DB.

Re: Text box updated from data entered into a listbox

Posted: Wed May 24, 2017 11:24 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Thu May 25, 2017 9:21 am
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 :-(

Re: Text box updated from data entered into a listbox

Posted: Thu May 25, 2017 2:36 pm
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 391 times

Re: Text box updated from data entered into a listbox

Posted: Thu May 25, 2017 2:53 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Thu May 25, 2017 3:24 pm
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.

Re: Text box updated from data entered into a listbox

Posted: Thu May 25, 2017 3:43 pm
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

Re: Text box updated from data entered into a listbox

Posted: Thu May 25, 2017 4:53 pm
by Maax555
Success, didnt realise strings are case sensitive.

many thanks

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

Posted: Tue Mar 13, 2018 4:48 pm
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

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

Posted: Tue Mar 13, 2018 7:13 pm
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

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

Posted: Wed Mar 14, 2018 10:39 am
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.