DCount Problem?

[BrokenBiker]

I've been doing a bit of looking around for some DCount/DLookup stuff to help w/ some auto-magic stuff on a new database. After the using enters a new record, I need to check for duplication of criteria. If so, I need to have it capture some info from the form and open a report. The code I have now simply opens a msgbox when there's duplication.

But, here's where the problem comes in. I have the VBA for a DCount function for one of the fields, and it works. When a duplicate entry is made in this field, during the BeforeUpdate the DCount works and opens a nice little message box...thanks to other threads on this forum. The code is below for the fields' BeforeUpdate function.

If DCount("[TailNo_Main]", "tbl_Main", "[TailNo_Main] = '" & Me.TailNo_Main & "'") > 0 Then
   MsgBox "Tail # is already listed.", vbOKOnly + vbCritical, _
              "DUPLICATE FOUND"
   'Cancel = True
End If

The problem w/ this is that it only checks the TailNo_Main field. What I actually need to do is check for matches between two different fields (TailNo_Main and WUC_Main). If there's a match between those two fields in a previous record, then I need to flag the user. A simple msgbox will be fine for now....I can fill in the details of the "flag" later.

Unfortunately, I can't seem to be able to use DCount or DLookup for multiple fields. I also haven't been able to locate any examples of this anywhere. Is it possible? Am I looking in the wrong direction for what I am trying to accomplish?

 

[pbaldy]

Good reference here:

http://www.mvps.org/access/general/gen0018.htm

 

[wazz]

Edit: the link from pbaldy is simpler (last one on the page). use below if you need criteria from different records.
================================
separate your dlookups from your message:

dim strTail as String
dim typeWUC as ?

strTail = DCount("[TailNo_Main]", "tbl_Main", "[TailNo_Main] = '" & Me.TailNo_Main & "'")
typeWUC = DCount(fill-in-the-blanks)

If strTail > 0 and If typeWUC > 0 Then
MsgBox "Already listed.", vbOKOnly + vbCritical, _
"DUPLICATE FOUND"
'Cancel = True
End If

 

[BrokenBiker]

Works like a champ! Thanks, wazz!

Now...I need to restrict the DCount search to the last six months. If you have some help for this, that'd be great. In the meantime, I'm gonna be doing some more research.

 

[BrokenBiker]

Got it! I just created a new query based on the table w/ a date criteria of Now()-365 and used that for the DCount.

Now I can identify repeat/recuring discrepancies based on tail number and system code, and then call an aircraft systems discrepancy history. Works like a champ!

Thanks for the help.