exclude value from combo box Record Source

[markjkubicki]

I want to select records from the table CNotes_EOS to be the Row Source of a combo-box when they are not already in the table where they will be stored (tbeCNotes_EOS)

tbeCNotes_EOS could have records already added from the table CNotes_EOS;


it may also have no records, or records that have been added by the end user and not fond in CNotes_EOS (and of consequence the values in the tbeCNotes_EOS.CN_ID would not be in the source table...)

when i do have at least (1) record in the table tbeCNotes_EOS with a value for CN_ID that is in CNotes_EOS this row source query is spot-on;

Not In (SELECT [CC_ID] FROM [tbeCNotes_EOS])

but, if there is none, the open form event fails

the code behind the form open is

Private Sub Form_Open(Cancel As Integer)
    Me.txtNoteText = ""
    Me.lblNoteText.Visible = False
    Me.txtNoteText.Visible = False
    Me.cmdAddNote.Visible = False
    Me.cmbCNoteTitle.Enabled = True
    Me.cmbCNoteTitle = ""
    Me.cmbCNoteTitle.Visible = True
    Me.cmbCNoteTitle.SetFocus
End Sub

any work-arounds, comments, or other suggestions would be greatly appreciated in advance,
mark

[June7]

Use a 'Find Unmatched' query. Join the tables (LEFT or RIGHT, not INNER). Then the criteria under CC_ID field would be Is Null. Use DISTINCT or GROUP BY to eliminate duplicates.

[markjkubicki]

perfect
thnx

new trick learned!!