Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Unanswered: Creating Unique Member ID number

    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?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    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)
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts