Combobox Not style

[Guest]

Hi people. I'm having trouble with a combobox; I'm not sure if this needs to
go in the "queries" posting, however.

I have a combobox and command button in a main form (frmWGBRESID), which
also has a subform(sfrmLINECOVERID). The command button assists the user
with adding the record selected by the combo box into the subform.

I want to create a dynamic combobox to reflect only records that have NOT
already been added to the subform, shortening the list of available
selections in the combobox with every addition the user makes to the subform.
The main form's recordsource is tblWGBRESID, the subform's recordsource is
tblLINECOVERID:

tblLINECOVERID (Subform)
LINECOVERID-PK
WGBRESID-FK to tblWGBRESID
LINKLINEID-FK to tblLINKLINEID

tblLINKLINEID
LINKLINEID-PK
LINKWGBID
LINENOID

tblWGBRESID (Main form)
WGBRESID-PK
WGBDATE
LINKWGBID

I thought I was on to something with the following SQL, but the following
code in fact shows only the records that have been added:

SELECT tblLINKLINEID.LINKLINEID, tblLINKLINEID.LINENOID
FROM tblLINKLINEID LEFT JOIN tblLINECOVERID ON tblLINKLINEID.LINKLINEID =
tblLINECOVERID.LINKLINEID
WHERE ((Not (tblLINKLINEID.LINKLINEID) Is Null) AND
((tblLINECOVERID.WGBRESID)=[Forms]![frmWGBRESID]![WGBRESID]))
GROUP BY tblLINKLINEID.LINKLINEID, tblLINKLINEID.LINENOID;

Anyway, if anyone can steer me in the right direction, I'd really appreciate
it.

accesskastle

 

[Ken Snell [MVP]]

Something like this, perhaps:

SELECT tblLINKLINEID.LINKLINEID, tblLINKLINEID.LINENOID
FROM tblLINKLINEID
WHERE tblLINKLINEID.LINKLINEID NOT IN
(SELECT T.LINKLINEID FROM tblLINECOVERID AS T
WHERE T.WGBRESID=[Forms]![frmWGBRESID]![WGBRESID]);