DLookup() for Multiple Fields Lookup

Topic

New Reply

Can DLookup() look up multiple fields?
tblProperty contains: PropertyID, Block, Floor and Flat. These 4 char fields compose a compound key.
Before Update event, I would like to ensure the newly added record won’t be a duplicate.
Armstrong

Reply | Quote

Replies

DLookup can look up only one field at a time, but you can specify multiple conditions. You could use DCount instead:

Code:

If DCount("*", "NameOfTable", "PropertyID=" & Chr(34) & Me.PropertyID & Chr(34) & _
	" And Block=" & Chr(34) & Me.Block & Chr(34) & _
	" And Floor=" & Chr(34) & Me.Floor & Chr(34) & _
	" And Flat=" & Chr(34) & Me.Flat & Chr(34)) > 0 Then
  MsgBox "This combination of PropertyID, Block, Floor and Flat already occurs!", vbExclamation
  Cancel = True
End If

The Chr(34) in the code is the double quote character ” that is needed around text values.

Reply | Quote

DLookup can look up only one field at a time, but you can specify multiple conditions. You could use DCount instead:

Code:

If DCount("*", "NameOfTable", "PropertyID=" & Chr(34) & Me.PropertyID & Chr(34) & _
	 " And Block=" & Chr(34) & Me.Block & Chr(34) & _
	 " And Floor=" & Chr(34) & Me.Floor & Chr(34) & _
	 " And Flat=" & Chr(34) & Me.Flat & Chr(34)) > 0 Then
   MsgBox "This combination of PropertyID, Block, Floor and Flat already occurs!", vbExclamation
   Cancel = True
 End If

The Chr(34) in the code is the double quote character ” that is needed around text values.

I’m amazed that you can reply my post less than 5 minutes! It took me more than 20 minutes to completely understand your Dcount().
You’re not only showing the mechanics of using Dcount() but also the trick of using it.
Thanks a lot.
Armstrong

Reply | Quote

Hi HansV,

Taking a long time to experiment with DCount(), eventually I succeeded the coding in macro to check the duplicate.
DCount(“*”, “tbl物業單位”,”BldgID = ctl_BldgID AND 座 = ctl_座 AND 層 = ctl_層 AND 室 = ctl_室”) > 0
The above macro is attached to the After Lost Focus event of the 4th text box (ctl_室 ).
My macro doesn’t have the Chr(34) things.
Thank you for showing me DCount().

Armstrong

Reply | Quote