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

[Hagar Delest]

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.

[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?

[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

[UnklDonald418]

I uploaded a simple demonstration with 2 subforms, Replaces and Similar.

[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

[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.

[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   Expand viewCollapse view

Sub RefreshForm(oEvent as object)

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

End Sub    'Refresh Form

[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

[Villeroy]

https://forum.openoffice.org/en/forum/v ... 49#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.

[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?

[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

[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   Expand viewCollapse view

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

[Maax555]

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

many thanks again.

[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   Expand viewCollapse view

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.

[Maax555]

Thanks Chrisb, I am however getting this error with or without the additional line in my project DB.

[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.

[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

[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 153 times

[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.

[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.

[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

[Maax555]

Success, didnt realise strings are case sensitive.

many thanks

[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

[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   Expand viewCollapse view

   if oForm.isNew then
      oForm.insertRow
   end if

[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.