Multiple DLookup - Access 2002 (1 Viewer)

[dcobau]

g'day,

I put a button on a form to add records but, before doing so, I want to check if a record with the same id numbers is already in the table. To do so I use the following code:

If (DLookup("[CompanyID]", "tblBranchAddress", "CompanyID = " & Me![CompanyID] And _
"ContactsID = " & Me![ContactID])) Then

MsgBox "The name of this contact has already been recorded."
Me.Undo

' Else

' DoCmd.OpenForm "frmBranchAddress"
' DoCmd.GoToRecord , , acNewRec
' Forms!frmBranchAddress!CompanyID = Me!CompanyID
' Forms!frmBranchAddress!ContactsID = Me!ContactID

' Forms!frmBranchAddress!BranchBld.SetFocus

End If

The form consists of tabbed pages being frmCompany (the main form) and frmContacts (the sub form). I get the error "Type mismatch".

Any ideas? thanks

Dave

 

[WayneRyan]

dc,

Two problems here ...

1) the AND clause is different from what you thought
2) the DLookUp returns CompanyID or NULL (success/failure)

If (Not IsNull(DLookup("[CompanyID]", _
                       "tblBranchAddress", 
                       "CompanyID = " & Me![CompanyID] & " And  " & _
                       "ContactsID = " & Me![ContactID])) Then ...

Wayne

 

[Pat Hartman]

Since the number of times a user attempts to add a duplicate is far fewer than the opposite, I would recommend trapping the duplicate error rather than testing beforehand. Always opt for the more efficient method and you are not likely to run into performance problems. There is also a remote possibility that between the time you check for a duplicate and the time that the record is actually inserted, someone else adds the same customer. That possibility means that the error trapping is also safer.

 

[dcobau]

thank you both

Dave