Combo box query not working

[ehsguy77]

I'm trying to build a combo box with a query value source. The combo box is in a subform called frmQueManifestItems, which is associated to frmQueManifests by a field called Manifest# with a one-to-many relationship.

In frmQueManifests, users select a Generator (autonumber) from tblGenerators with a combo box called GenID. This GenID# is stored in tblQueManifests.

Now, tblGenerators is associated to tblGenApprovals by GenID in a one to many relationship - there are several ApprovalCodes in tblGenApprovals for each generator in tblGenerators.

In my subform frmQueManifestItems, I have a combo box in which I would like to see only ApprovalCodes from tblGenApprovals for the GenID selected in frmQueManifests.

I've been trying to accomplish this with a row source query in the ApprovalCode combo box by building the query with tblGenApprovals and the ApprovalCodes and GenID fields, with criteria in the GenID field set to =Forms!frmQueManifests!GenID.

It isn't working. What am I doing wrong?

 

[PHV]

Something like this ?
In the Current event procedure of frmQueManifestItems:
Me!ApprovalCode.RowSource = _
"SELECT A.ApprovalCodes, A.GenID" & _
" FROM tblQueManifests M INNER JOIN tblGenApprovals A" & _
" ON M.GenID = A.GenID" & _
" WHERE M.[Manifest#]='" & Me![Manifest#] & "'"
If Manifest# is defined as numeric, get rid of the single quotes.
It isn't working.
Some error message ? Unexpected behaviour ?
What am I doing wrong?
Difficult to say as we can't know what you did (no sql nor code posted)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244