Selecting Values from a ListBox

[MattJM]

My problem is how to use a listbox that allows a user to select a name but then stores a number in the underlying table. For example, given a table containing 2 fields, a Supplier ID and a Supplier Name, I want to have a drop down box on a form that allows a user to select the Supplier by the name, and always displays the name on the form, but the information recorded on the table is the ID.

I have one form where this works but I can't reproduce it. The working drop down was created using the Form Wizard. The Supplier Table is connected to an Order table, and at first there was a lookup for the Supplier Name. The form was created, and afterwards the lookup was changed to fetch the ID but the Order form continued to look up and display the Name. Now the user can select the name and if you look at the table the relevant ID is stored.

How can I reproduce this in other forms?

Thanks in advance,
Matt

 

[rbowes]

You could delete the control and start the wizard again.

OR

You could adjust the current combo box to do what you want.
I think what you're looking for can be done by doing this:

In design view, select the combo box and open the property sheet. Under Data, select the Row Source property and click the elipse that appears. This should open the query designer. If there is anything just delete it and place the supplierID, SupplierName in the grid in that order. Exit and save the sql statement. Press "Save" on the form (back in design View) Now, back to the combo box's property sheet, Set:

column count: 2
Column Widths: 0";2"
Bound Column:1
Save the form and Exit design view

What that does is set Sql statement for the row source of the combo box to SupplierID and SupplierName. You set the combo box to display two columns, but by setting the first column to a width of 0", you have hidden it from view (its still there though). If you bind the combo box to a field in your database it is bound to the hidden column. Also, the value of combo box is derived, by default, to the first column. So, if you select "CompanyZ" (with supplierID = 26) the value of the combo box is "26".

Does that help?

Let me know.

Bob

 

[MattJM]

Yeah that helped loads thanks very much, that was exactly what I was after!

Why is it that it's always the little tricks that save so much time and are the hardest to find?!

Thanks very much again,
Matt