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
– bonCodigo, Commented Jun 4, 2015 at 2:33

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

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

@Tash Don't forget to accept it as the answer so future users are able to find it!

Collectives™ on Stack Overflow

Data mismatch error 3464

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related