Look Up Combobox

[jmilley]

I'm tryimg to find a way to do the following:

I have a table called Products, which contains a listing of my products. For some of these products I have individual plats stored by serial number, in a table called plants.
I want the user to enter the productID for the products table, and then present them with a list of all the serialNumbers associated with that productID.

The two tables are linked by ProductID

Right now what i have gives a listing of all serial numbers in the plants table.

Thanks

 

[Jonathan Kok]

Assuming you're entering the information on a form, could you not create code that will change the RowSource property of your serialnumber list box to list only those products with the appropriate ProductID? You would put this under the OnExit property of your ProductID field. The code would look something like this:

Private Sub ProductID_Exit()
[SerialNumber].RowSource = "SELECT DISTINCTROW [Plants].[SerialNumber] FROM [Plants] WHERE [Plants].[ProductID] = " & Chr$(34) & [ProductID] & Chr$(34)
End Sub

Just an idea...Good Luck!