Hi - Where am I going wrong??

I have a table called PLPriceList which has the following fields
ProductID
ProductCode

I have a second table - PJBOQ which has the field [ProductID]
I have a form to allow data entry in to this table.
This is a continuous form.
I enter the ProductId and I want the relevant Product Code to be returned in a list box
The Row Source Of The List Box Reads

SELECT [PLPriceList].[ProductCode] FROM [PLPriceList] WHERE [PLPriceList].[ProductID] =[ProductID]

The list box just shows all the Product Codes rather than the one matching the Product ID

Any Ideas

I'm not really sure about the usefulness of using an ID to look up a Code?
Are you saying your users know the Id but not the code?


But to do this , you need a textbox to enter the ID, and then you need code to refresh the listbox.


You need to give the textbox a unique name (different to the id field) say, txtProductID.

Then you set the rowsource of the listbox to..

SELECT [PLPriceList].[ProductCode] FROM [PLPriceList] WHERE [PLPriceList].[ProductID] =forms!yourformname!txtProductID


In the afterupdate event procedure of the textbox you do..

Me!listboxname.requery


But unless you have multiple ProductCode values for the same ID then I can't see the point of using a listbox at all.

Hi Peter
Maybe Im approaching this wrongly.

In essence I want the user to be able to add info (eg the product ID) and the db to return various info about this product (eg price)

The user can then add further detail eg quantity and db returns eg total price.

The form must be continuous and the data that the user adds must be stored in the under lying table.

This does not sounds like something you would use a listbox for.
It sounds more like a combo box requirement.

And the basic idea of a combo is that the user selects from the list. Aany 'input' to a combo is only a means to homing in on the required item.

But where is the product price info stored? You don't mention it in your table descriptions- I would have thought it would be part of PLPriceList .

You need to build the combo to include all the fields you want to extract (such as id, code, description, price).
The combo can show any or all of the fields or just one field if that's all you want the user to see. You just adjust the column widths to 0 for columns you don't want to see.
Any characters input will work on the first visible column and will cause the dropdown list to move to the first item that starts with the input characters.

You need to decide which field value (from the combo) is to be stored in the PJBOQ table and you can nominate this field as you go through the combobox wizard.

For values that need to appear in the record once a selection has been made from the combo you have to write code in the combo's afterupdate event procedure.

So for example you can say...

me.txtpricetextbox= me.comboname.column(2)

This puts the THIRD column into a textbox called txtPricetetxbox (in code columns start from 0, not 1).


You can see an example of this in Northwind. If you have A2007/2010 then look at the Order Details form; if you have A2003 or earlier look at the Orders form.