Data mismatch error 3464

Question

I'm very new to VBA so I'm not even sure if I'm heading in the right direction. I'm using Access 2010 and I've created a form where you search for an ID and then you click to add a new record for multiple timepoints (e.g follow-up form for timepoint 1, 2, 3, 4).

I have a StudyPeriod field (long integer) where you select from a list (via query number + text). I want an error box to come if that time period has already been entered.

I've been trying to use this code but it keeps coming up with the 3464 runtime error and the de-bug highlights the If Me. line.

What am I doing wrong?

Private Sub StudyPeriod_AfterUpdate()
    Dim StudyPeriod As String
    Dim StLinkCriteria As String

    StudyPeriod = Me.StudyPeriod.Value
    StLinkCriteria = "[StudyPeriod] = " & "'" & StudyPeriod & "'"
    ' If line below returns error
    If Me.StudyPeriod = DLookup("[StudyPeriod]", "3_Questionnaire", StLinkCriteria) Then
        MsgBox "This questionnaire has already been entered for this participant." _
            & vbCr & vbCr & "Please check RegID or Summary table.", vbInformation, _
            "Duplicate information"
        Me.Undo
    End If
End Sub

MsgBox DLookup("[StudyPeriod]", "3_Questionnaire", StLinkCriteria) See what you get here. — bonCodigo

HansUp HansUp · Accepted Answer · 2015-06-04T04:36:37

Your StudyPeriod field in your 3_Questionnaire table is a numeric datatype (Long Integer). So do not include quotes before and after the value of your StudyPeriod variable when you build the StLinkCriteria string:

'StLinkCriteria = "[StudyPeriod] = " & "'" & StudyPeriod & "'"
StLinkCriteria = "[StudyPeriod] = " & StudyPeriod
If Me.StudyPeriod = DLookup("[StudyPeriod]", "3_Questionnaire", StLinkCriteria) Then

Data mismatch error 3464

1 Answers