Combo box "lookup" criteria problem

[Joe Cletcher]

I have a combo box in a second level subform that I am trying to set the
lookup query to include criteria that using the unique identifier [an
automatic number (unique and indexed) that is not the primary key] from the
main form or the first level subform.

I have drawing (Dwgs), drawing revisions (DwgRevs), lot numbers (LotNmbrs),
serial numbers (SnNmbrs) and serial numbers contained in a lot number
(SnsInLot) table. The table structures are as follows:

• Dwgs
DwgID (auto number and primary key)
Indexed (no duplicates)
DwgNmbr (50 character text)
Indexed (no duplicates)
DwgTitle (255 character text)
Indexed (duplicates OK)
• DwgRevs
DwgRevID (unique auto number)
Indexed (no duplicates)
DwgID (long number from “Dwgs†table—primary key)
Indexed (duplicates OK)
Rev (2 character text—primary key)
Indexed (duplicates OK)
• LotNmbrs
LotID (unique auto number)
Indexed (no duplicates)
DwgRevID (long number from “DwgRevs†table—primary key)
Indexed (duplicates OK)
Lot (long number—primary key)
Indexed (duplicates OK)
• SnNmbrs
SnID (unique auto number)
Indexed (no duplicates)
DwgRevID (long number from “DwgRevs†table—primary key)
Indexed (duplicates OK)
SN (long number—primary key)
Indexed (duplicates OK)
• SnsInLot
SnsInLotID (unique auto number)
Indexed (no duplicates)
LotID (long number from “LotNmbrs†table—primary key)
Indexed (duplicates OK)
SnID (long number from “SnNmbrs†table—primary key)
Indexed (no duplicates)

The main form is based on table “DwgRevs†and is linked to the first level
subform using the field “DwgRevID†in tables “DwgRevs†and “LotNmbrsâ€. The
second level subform is linked to the first level subform using the field
“LotID†in tables “LotNmbrs†and “SnsInLotâ€. The select query for the combo
box bound to data field “SnID†in table “SnsInLot†works fine if no criteria
are defined. However, my serial number choices (previously defined in table
“SnNmbrsâ€) include serial numbers for drawing revisions with “DwgRevID†not
equal to the “DwgRevID†in table “LotNmbrsâ€.

The working “select query†follows:

SELECT SnNmbrs.SnID, "S/N " & Trim([SN]) & " " & Trim([DwgNmbr]) & " Rev " &
Trim([Rev]) AS DwgRv, SnNmbrs.DwgRevID FROM Dwgs INNER JOIN (DwgRevs INNER
JOIN SnNmbrs ON DwgRevs.DwgRevID = SnNmbrs.DwgRevID) ON Dwgs.DwgID =
DwgRevs.DwgID ORDER BY SnNmbrs.DwgRevID, SnNmbrs.SN;

The non working “select query†follows:

SELECT SnNmbrs.SnID, "S/N " & Trim([SN]) & " " & "-" & Trim([DwgNmbr]) & "
Rev " & Trim([Rev]) AS DwgRv, SnNmbrs.DwgRevID FROM Dwgs INNER JOIN (DwgRevs
INNER JOIN SnNmbrs ON DwgRevs.DwgRevID = SnNmbrs.DwgRevID) ON Dwgs.DwgID =
DwgRevs.DwgID WHERE (((SnNmbrs.DwgRevID)=[Forms]![DwgRevs]![LotNmbrs
subform].[Form]![DwgRevID])) ORDER BY SnNmbrs.DwgRevID, SnNmbrs.SN;

What am I doing wrong? The value from "[Forms]![DwgRevs]![LotNmbrs
subform].[Form]![DwgRevID]" always seems to point to the first "DwgRevID" in
table "SnNmbrs".