populating a subform from combo box

[rick roberts]

i have a table that contains data on products tblProducts holding ProductID, ProductDescription, Price etc...
i have a subform that has text boxes relating to these fields. the ProductID being the unique number and ProductDescription being a combo box
i want to select a product from the combo box and let the other textboxes populate from the information relating to that product -- using the ProductID as a reference. ive tried DLookup and SQL statements in the control sources of the text boxes and am failing to get a result

below are what ive already tried
DLookUp("UnitPrice","Products","ProductID = " & [ProductID])
SELECT tblProducts.OrderCode FROM tblProducts WHERE ProductID = tblProducts.ProductID

the most common error im getting is
The object doesnt contain the Automation Object tblInvoice
tblInvoice is where the main form gets its info from

can anybody please help me with this
thanks in anticipation

 

[jim84]

i want to select a product from the combo box and let the other textboxes populate from the information relating to that product

do you mean you want the subform to populate or have you got text boxes that need to populate from the combo box as well?

 

[rick roberts]

i need textboxes within the subform to populate in relation to whatever i choose from the combo box - each line of the subform will be a different item from previous (i have had it working where, each time i change one line, the other lines follow that change)

 

[neileg]

Use the selection in the combo box as the criterion for a query. Base the subform on the query. The syntax for the criterion is:
Forms!MyFormName!MyComboBoxName

 

[rick roberts]

but the combo box is in the subform - do i select the combo box from the table and then select textboxes from the query???

ive tried - Control source for product descrition = [tblProducts]![ProductDescription]
then for control source for Cart Number =[qryPopulate]![CartNo]
where qryPopulate is as you suggested

it leaves me a #Name? error

 

[neileg]

Getting a bit lost here. Anychance you could post a stripped down and compacted version of your db?

 

[rick roberts]

here is a trimmed version of the database so far
i do have a working version of this -- but found that to create astock control i have had to go back to the beginning
hope you can help