Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Assigning an alphanumeric value (2003)

    I've got a data entry form, based on a table "Players," that I use to add new personnel to our database. The primary key field for the table, "ID," is a Text Field and consists of the person's initials (or an "x" if we do not know the middle initial) and a three-digit number. For example: John R. Jenkins would be jrj001, Jason R. Jones would be jrj002, John Smith would be jxs001, and so on. After inputting the person's name and contact information, the user clicks a command button (cmdDone), which would determine the correct ID value for that person.

    Here's the code I use to assign the alpha characters:

    Dim stFI As String
    Dim stMI As String
    Dim stLI As String
    Dim stInitials As String

    'Sets the alpha characters
    stFI = LCase(Left(FirstName, 1))
    If IsNull([MiddleName]) Then
    stMI = "x"
    Else
    stMI = LCase(Left(MiddleName, 1))
    End If
    stLI = LCase(Left(LastName, 1))
    stInitials = stFI & stMI & stLI

    I cannot figure out how to assign the numeric characters. Please help- thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Assigning an alphanumeric value (2003)

    Try this:
    <code>
    ' Existing code
    ...
    stInitials = stFI & stMI & stLI

    Dim intCount As Integer
    Dim stID As String
    ' Count number of existing records with these initials
    intCount = DCount("*", "Players", "ID Like " & Chr(34) & stInitials & "*" & Chr(34))
    ' Add 1
    intID = intID + 1
    ' Create ID
    stID = stInitials & Format(intID, "000")

    ' Rest of the code goes here
    ...</code>

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Assigning an alphanumeric value (2003)

    As always, thanks for your quick help. I foresee one problem with simply counting the number of records with the same initials. Take the following case, for example.

    There are 3 people with the initials abc- their IDs would be abc001 through abc003. Now let's say that abc001 and abc003 quit; abc002 remains in the database. The next time I add someone with those initials, the code will see that there is one person with the initials abc and make the new person abc002, which would violate the primary key.

    Is there a way to have the code determine the highest number and add one to that?

    Thanks again!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Assigning an alphanumeric value (2003)

    Try this:
    <code>
    intCount = CInt(Right(DMax("ID", "Players", "ID Like " & Chr(34) & stInitials & "*" & Chr(34)), 3))
    </code>
    The code looks up the maximum value of those IDs that begin with the supplied initials, extracts the last three characters and converts them to an integer.

  5. #5
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Assigning an alphanumeric value (2003)

    Works great- thanks!

Posting Permissions

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