Calculating the primary key

[Jeff]

I received some help a while back on this. I am trying to calculate a
primary key based on first initial, last initial, and a sequential 3
digit number. This is for an existing system that I inherited.

Right now, the subroutine is called after the first name or last name
fields are changed.

Private Sub Last_Name_BeforeUpdate(Cancel As Integer)
If IsNull(Me![Member ID]) Then
Me.[Member ID] = Left(Me.[First Name],1) & Left([Last Name],1) _
& Format(Nz(DMax("Val(Mid([Member ID],3)", "Member Data", _
"Left([First Name],1)='" & Left(Me.[First Name],1) & "' And "
_
& "Left([Last Name],1)='" & Left(Me.[Last Name],1) & "' ") _
, 0)+1, "000")
End If
End Sub


I'm not seeing any issues with it, but I'm getting a syntax error
message whenever it is called. Can someone tell me what I'm doing
wrong?

Thanks

Jeff

 

[Jeff Boyce]

Jeff

It might help us to know what that error message says...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I received some help a while back on this. I am trying to calculate a
primary key based on first initial, last initial, and a sequential 3
digit number. This is for an existing system that I inherited.

Right now, the subroutine is called after the first name or last name
fields are changed.

Private Sub Last_Name_BeforeUpdate(Cancel As Integer)
If IsNull(Me![Member ID]) Then
Me.[Member ID] = Left(Me.[First Name],1) & Left([Last Name],1) _
& Format(Nz(DMax("Val(Mid([Member ID],3)", "Member Data", _
"Left([First Name],1)='" & Left(Me.[First Name],1) & "' And "
_
& "Left([Last Name],1)='" & Left(Me.[Last Name],1) & "' ") _
, 0)+1, "000")
End If
End Sub


I'm not seeing any issues with it, but I'm getting a syntax error
message whenever it is called. Can someone tell me what I'm doing
wrong?

Thanks

Jeff

Probably it's being called when the LastName is selected *BEFORE* the First
Name has been entered, or vice versa. It will only work if both fields have
been filled in. In addition to checking for a null Member ID you need to check
to see if the other name has been entered.

I'd also strongly recommend using the AfterUpdate event rather than
BeforeUpdate.

Something like:

Private Sub Last_Name_AfterUpdate()
Dim sLast As String
Dim sNext As String
Dim iNext As Integer
' Check to see if both LastName and FirstName exist
If Len(Me![First Name]) = 0 OR Len(Me![Last Name]) = 0 Then
Exit Sub
Else
If Len(Me![Member ID] = 0 Then
' get the two letters for this record
sNext = Left(Me![Last Name], 1) & Left(Me![First Name], 1)
' find largest ID starting with these letters
sLast = NZ(DMax("[Member ID]", "[Member Data]", _
"[Member ID] LIKE '" & sNext & "*'"), "ZZ000")
iNext = Val(Mid(sLast, 3))
If iNext = 999 Then
Msgbox "Turn off computer and go home, database full", vbOKOnly
Else
sNext = sNext & Format(iNext + 1, "000")
Me![Member ID] = sNext
End If
End If
End If
End Sub

Untested air code...

Note that there should also be code in the Form's (not any textbox's)
Beforeupdate event to check to see if last name, first name and ID have been
filled in - there's nothing to stop a user form opening the form, filling in
some fields, and leaving one or other or both name fields blank!