Update other fields when select item on List Box

[ipaqAccess]

Hi all,

I am newbie and trying to create a Form to checking the arrival/amount on stock status of a list of goods.

So I have:
Table tbl_parts with part number and description which Part number is the key.
Table tbl_arrival with Part number and ship date, arrival date, and delivery date ,
Table tbl_amount with Part number and On hand, sign out.

I created a form with 2 list boxes are Part number and Description and textboxes are shipdate, arrival date, delivery date, on hand, sign out.
How can I make other text boxes auto display the current value whenever I select Part number/Description on the list box?

Tried to search the forum but cant find any solution.

Thanks a lot for your help.

 

[John Big Booty]

Welcome to the forum.

You can use the following in the Control Source of your Text boxes;

=ListBoxName.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x is the column number that holds the data you wish to appear in the text box. The column may be a hidden column in the listbox. Remember that the columns of a Listbox, or combo box for that matter, are numbered from Zero up. The first (zero) column is often hidden and contains the bound data.

 

[ipaqAccess]

Hi,

Thanks much for your help.
Currently I am using Dlookup to do what I wanted to do. But it required a bit of coding which i am not preferred. I would prefer simple settings that can do the work and leave the programming for more complex task.

According to your solution, the Listbox should contain more than 1 field? My list box currently only display the item in one field. So if I do as your guide, the column would display the 1st value of the field.

E.g: Part number (column 0) 1 Ship date (column 1) is 19 jun, Part number 2 Ship date is 20 Jun.

I tried to put two Fields (columns) in to one list box but couldn't.
If I put the Control Source of the Textbox Ship date =ListBoxName.Column(0) then the Field Shipdate will show the Part number.
If I put the Control Source of the Textbox Ship date =ListBoxName.Column(1) then the Field Shipdate will show the Shipdate is 19 Jun only even if I select Part number 2 in the list box.

Any ideas where I went wrong?
Thanks a lot!

 

[John Big Booty]

Have a look at the attached sample, perhaps that will help.

 

[ipaqAccess]

It's helped. Appreciated for your time.

In summary for anybody have the same problem as mine, there are 2 ways to get to this. I think your way is fast and clean for forms with a few columns, but if the form required more than 10 text boxes then I think using Dlookup is easier to manage.

Thanks a lot for your help