Check multiple columns for combo box value

[JamesN]

Hi,

I have a user input form and I need to prevent duplicate references being added. In the after update of the combo box selection I would like the form to flag if the reference chosen is a duplicate or whether it has not been entered.

The difficulty I have is that I am checking 3 columns for this reference no. The combo box with the reference no is named 'caseid'. Columns in the table which need to be checked are 'caseid', 'accountno' and 'ir'.

Tried to use a dlookup but only used this to check one column not multiple.

Thanks

James

 

[Ranman256]

in addition to the combo box put 2 more text boxes.
when the user picks the item in the combo, fill the text boxes with the other items in the combo.
then use these values.

sub cboBox_afterupdate()
   txtBox1 = cboBox.column(1)
   txtBox2 = cboBox.column(2)
end sub

 

[sneuberg]

I think DCount is easier to use for this sort of thing than DLookup but whatever in once case we check three tables for duplicates without any problems so just write three DLookups or DCount something like

If DCount( "*", "[The table name]", "[caseid] = & Me.ComboBoxName) > 0 Or _
    DCount( "*", "[The table name]", "[accountno'] = & Me.ComboBoxName) > 0 Or _
     DCount( "*", "[The table name]", "[ir] = & Me.ComboBoxName) > 0 Then

  'the code
End If