All tables in database are linked by Autonumber field called Access ID, this of course is created automatically when a new record is created. They have another field called Membership ID that they create manually by taking first two letters of last name and assigning a number value to them; example: Smith = s would be 7 and m would be 1 so member number would start with 71, then they go to the table and sort all by Member id and find the last number used beginning with 71 and assign the next number so if 711234 was the last assinged number this one would be 711235 and they they enter that number manually into the membership ID field.
Can anyone think of a better way to create this Membership ID field that would be automatic and less manual intensive?
Why don't you simply use the Access ID? I know that it's not recommended to use a surrogate key for anything but establishing relationships, but in this case and as far as I know of your database I don't see any reason why you could not use it. It's not as if the Membership ID's should necessarily be continuous or are subject to change.
Otherwise, your system is not that complex and can easily be implemented, except that there are 24 letters in the alphabet, so you'll need 2 digits for each letter or assign the same digit to several letters. Here I chose the first solution and opted for a Member ID of type text to keep the leading zero:
Function NewID(ByVal LastName As String) As String
Dim strNewID As String
Dim lngLastID As Long
LastName = UCase(LastName)
strNewID = Format(Asc(Left(LastName, 1)) - 64, "00") & Format(Asc(Mid(LastName, 2, 1)) - 64, "00")
lngLastID = Nz(DMax("MembershipID", "Tbl_Membership", "MembershipID Like '" & strNewID & "*'"), 0)
NewID = strNewID & Mid(Format(lngLastID + 1, "00000000"), 5)