P
paulu
I have a database for members of a junior sporting club. As part of that I
have a form which can be used to edit existing records or you can click a
button to enter a new record. Each member has a unique code which is the
first 3 letters of their last name and a 3 digit number so that person
Phillips might have a code PHI001 and another person Phillips PHI002. If I
add an entry for a third person Phillips I want to run a procedure that
checks that there are no other records with the same last name, first name
and birth date. Then create the code PHI003 if the new record is unique. I
also have to consider that last names other than Phillips will have a code
starting with PHI such as Phipps for example.
The code I have put together may be a bit clumsy however it 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 & CodeNumber
Me!MemberCode = MCode
Me!FirstMembershipYear = Format(Now, "yyyy")
Else
Msg = "This person is an existing member! Do you wish to 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
However I get a run time error '2001' with the message 'you cancelled the
previous operation'
any help would be appreciated
Cheers
have a form which can be used to edit existing records or you can click a
button to enter a new record. Each member has a unique code which is the
first 3 letters of their last name and a 3 digit number so that person
Phillips might have a code PHI001 and another person Phillips PHI002. If I
add an entry for a third person Phillips I want to run a procedure that
checks that there are no other records with the same last name, first name
and birth date. Then create the code PHI003 if the new record is unique. I
also have to consider that last names other than Phillips will have a code
starting with PHI such as Phipps for example.
The code I have put together may be a bit clumsy however it 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 & CodeNumber
Me!MemberCode = MCode
Me!FirstMembershipYear = Format(Now, "yyyy")
Else
Msg = "This person is an existing member! Do you wish to 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
However I get a run time error '2001' with the message 'you cancelled the
previous operation'
any help would be appreciated
Cheers