VBA in Access - Duplicate Entries Msg Box-CodePudding

Can anyone help me identify what's wrong with this code? If I change DOB in the table to short text and DIM it as a string, the code works. But, with DOB as a date field, DIM as date, I am getting this error message.

"Run-time error '3464' Data Type mismatch in criteria expression"

When I click on debug, the line bolded below is highlighted in yellow.

Private Sub DOB_AfterUpdate()
Dim DOB As Date
Dim FirstName As String
Dim LastName As String
Dim stLinkCriteria As String
Dim PIN As Integer

'Assign the entered customer name and  address  to a variable
NewJII = Me.FirstName.Value
NewJII2 = Me.LastName.Value
NewDOB = Me.DOB.Value
stLinkCriteria = "[FirstName] = " & "'" & NewJII & "' and [LastName] = " & "'" & NewJII2 & "' And [DOB] = " & "'" & NewDOB & "'"

**If Me.FirstName & Me.LastName & Me.DOB = DLookup("[FirstName]", 
"TblPersonLog", stLinkCriteria) Then**

  MsgBox "This Customer, " & stLinkCriteria & " has already been entered in database." _
            & vbCr & vbCr & "with DOB " & NewDOB & "" _
            & vbCr & vbCr & "Please check Customer name and Date Of Birth again.", vbInformation, "Duplicate information"
   Cancel = True
End If



End Sub

Really appreciate any guidance!! I've been trying to troubleshoot this for days. Not very strong in VBA.

CodePudding user response：

First, you can't use DLookup that way. Second, if you wish to cancel, use the BeforeUpdate event. Third, as stated by Tim, use the correct syntax for the date criteria.

Thus, it could be something like this:

Private Sub DOB_BeforeUpdate(Cancel As Integer)

    Dim FirstName       As String
    Dim LastName        As String
    Dim NewDOB          As Date
    Dim stLinkCriteria  As String

    If Not IsNull(Me!DOB.Value) Then
        ' Assign the entered customer name and DOB to variables.
        FirstName = Nz(Me!FirstName.Value)
        LastName Nz(Me!LastName.Value)
        NewDOB = Me!DOB.Value

        stLinkCriteria = "[FirstName] = '" & FirstName & "' And [LastName] = '" & LastName & "' And [DOB] = #" & Format(NewDOB, "yyyy\/mm\/dd") & "#"
        Cancel = Not IsNull(DLookup("[FirstName]", "TblPersonLog", stLinkCriteria))

        If Cancel = True Then
            MsgBox "This Customer, " & FirstName & " " & LastName & ", has already been entered in the database" _
                & vbCr & vbCr & "with DOB " & NewDOB & "." _
                & vbCr & vbCr & "Please check Customer name and Date Of Birth again.", vbInformation, "Duplicate information"
        End If
    End If 

End Sub