Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Making an ID from a person first 4 letters (2003)

    Hi, I breaking my head trying to get a VBA code that could make a Primar key ID that takes the first 4 letters of a person last name and combine it with last four number of his personal Identification.

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

    Re: Making an ID from a person first 4 letters (2003)

    If you have a field LastName and a field PersonID, the expression
    <code>
    Left([LastName], 4) & Right([PersonID], 4)
    </code>
    will return the value you want. But you haven't told us how and where you want to use this.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Making an ID from a person first 4 letters (2003)

    I have a form that ask to enter the LastName, and PersonalID and adding new individuals. when the add command goes it picks the info and takes you to a new form having the ID field ready for you. I want to remove that extra step by removing that initial form and let the ID field takes the info automatically as soon I enter. The problem that i came with is trying to import other people data (historic invoces, scheduling) and add it a database with the same structure. Some people have already the ID in the other database. Some i want to make a unic ID for the specific individual that may have the same name.

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

    Re: Making an ID from a person first 4 letters (2003)

    OK. Was the info I gave you enough to help you on your way? If not. please try to tell us exactly how you want to use it.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Making an ID from a person first 4 letters (2003)

    Here is the database that i am starting. When you go to the form. InstructorInputData. In the field Instructor ID. i would like the field to pull the information automatically from the name first 4 letters and last 4 from the personnelid.

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

    Re: Making an ID from a person first 4 letters (2003)

    Instead of having a field InstructorID in the table, you could make it a calculated field: set the Control Source of the text box to
    <code>
    =Left([InstructorName],4) & Right([PersonalID],4)
    </code>
    It will automatically be updated if you enter or modify the InstructorName or PersonalID.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Making an ID from a person first 4 letters (2003)

    I when to the form and added the expression to the control source. The information is automatically typed in the instructorID. But when i try to enter another person. a message box pop indicating that the field instructorID cannot contain a null value.

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

    Re: Making an ID from a person first 4 letters (2003)

    You should remove the InstructorID field from the table, since it is now a calculated field.

  9. #9
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Making an ID from a person first 4 letters (2003)

    I want to keep this field as the primary key when exporting data to a database with the same structure. I put the expression on a textbox that will be no visible. I would like to use vba code to transfer the information from the textbox to the InstructorID as the data was typed. Sorry for the inconvinience, Hans I really appreciate all your help.

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

    Re: Making an ID from a person first 4 letters (2003)

    If you want to keep the field, you must set the Control Source of the text box back to InstructorID. This is important!

    Next, create After Update event procedures for InstructorName and PersonalID. Here are detailed instructions for the former.

    - Open the form in design view.
    - Select the InstructorName text box.
    - Activate the Event tab of the Properties window.
    - Click in the After Update box.
    - Select [Event Procedure] from the dropdown list.
    - Click the builder button (the ...) to the right of the dropdown arrow.
    - Make the code look like this:

    Private Sub InstructorName_AfterUpdate()
    Me.InstructorID = Left(Me.InstructorName, 4) & Right(Me.PersonalID, 4)
    End Sub

    - Perform the same steps for PersonalID.
    - When you're done, switch to datasheet view to test.

    The above will only affect existing records if you edit them, and new records as they are being entered. It will not automatically update existing records. If you have to update existing records, you'll need an update query.

  11. #11
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Making an ID from a person first 4 letters (2003)

    Your did it again. This is perfect. Thanks Hans.

Posting Permissions

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