Help with error

[Guest]

I have a some which I want to check that a new record is not already in the
Members table and then generate a new member code by taking the first three
letters of the persons last name and adding one(1) to the number of existing
member with the same 3 letter member code prefix . However I get a run time
error '2001' with the message 'you cancelled the previous operation'
My code is as follows;

-----------------------------------------------------------------------------------------

Private Sub Birthdate_AfterUpdate()
Dim LastNameCode As String, CodeNumber As String, FCodeNumber As String
Dim BDate As Date, FName As String, MCode As String

BDate = Me!Birthdate
FName = Me!FirstName

If (IsNull(DLookup("[LastName]", "Members", "[BirthDate] =#" & _
BDate & "# And [FirstName] ='" & FName & "'"))) Then

LastNameCode = Left(Me!LastName, 3)

CodeNumber = 1 + DCount("[MemberCode]", "Members", "&_
Left(Me!MemberCode,3) ='" & LastNameCode & "'")

FCodeNumber = Format(CodeNumber, "000")

MCode = LastNameCode & FCodeNumber

Me!MemberCode = MCode
Me!FirstMembershipYear = Format(Now, "yyyy")
Else

Msg = "This is an existing member! Record member activity" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Existing Member" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
'Open the members action entry form
DoCmd.OpenForm "frmMemberActionsEntry", acNormal, , , acFormAdd,
acWindowNormal
Else ' User chose No.
DoCmd.Close ' Close the form.
End If
End If

End Sub

 

[Allen Browne]

Which line generates the error?

If it is the DLookup(), it means that one of the arguments is malformed.
Work with it in the Debug window until you get it to work.

It may help to create a string variable for the 3rd argument, and have
Access show you what it produced when it failed, i.e.:
Dim strWhere As String

If Not IsNull(Me.BDate) Then
strWhere = "([BirthDate] = " Format(Me.BDate, "\#mm\/dd\/yyyy\#) & _
") And ([FirstName] = """ & Me.FName & """)"
Debug.Print strWhere
If (IsNull(DLookup("LastName", "Members", strWhere)) Then