Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    generating ID numbers (Access03)

    If people are classified as a doctor, contractor, or guest I want the database to automatically to look for the next available number in their category and generate their number. I have the following code. But it isn't working. Can someone help me. Thank you.

    Private Sub txtClassification_AfterUpdate()
    Dim NewID As String
    Dim OldID As String
    If txtClassification = "Doctor" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='D'"), "0000")
    NewID = Format("DR-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    ElseIf txtClassification = "Contractor" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='A'"), "0000")
    NewID = Format("C-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    ElseIf txtClassification = "Guest" Then
    OldID = Nz(DMax("Right(LearnerID,4)", "tblLearners", "left(LearnerID,1)='G'"), "0000")
    NewID = Format("G-") & Format(Right(OldID, 4) + 1, "0000")
    Me.LearnerID = NewID
    End If
    End Sub


    Fay

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

    Re: generating ID numbers (Access03)

    You haven't told us what exactly the problem is:
    - You get an error message (if so, what does it say).
    - The result is different from what you intended.
    - The code doesn't do anything at all.

    Shouldn't the condition in DMax for contractors be <code>"Left(LearnerID,1)='C'"</code> ?

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: generating ID numbers (Access03)

    Fay,
    What do you mean that it isn't working. I created a simple table and form and used your code as posted and it seem to work perfectly.

    (except for the Contractor as Hans pointed out - didn't catch that one <img src=/S/beep.gif border=0 alt=beep width=15 height=15> )
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: generating ID numbers (Access03)

    Sorry just got out of teaching a class.

    Sorry I was frustrated and didn't post properly. When I tab out of txtClassification the LearnerID (Employee #) isn't generated. No error messages I fixed the other issue. Thank you. Fay

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

    Re: generating ID numbers (Access03)

    Click in the line

    Private Sub txtClassification_AfterUpdate()

    and press F9 to set a break point. When you enter something in txtClassification and press Tab, the code should pause at the breakpoint. You can press F8 repeatedly to single-step through the code. You can hover the mouse pointer over a variable to see its value. Does that give a clue? If not, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: generating ID numbers (Access03)

    I stepped through and it works that way. But, there is always one of those. Anyway. When I tab out of txtClassification the number doesn't appear until after I tab through Learner ID(Employee #). Even though the After Update function is set to txtClassification. Thanks for the help. Do you think I should still send a stripped down version?

    Fay

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

    Re: generating ID numbers (Access03)

    Yes, please.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: generating ID numbers (Access03)

    Sorry. Here is the database. Thank you. Fay
    Attached Files Attached Files

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

    Re: generating ID numbers (Access03)

    The text box bound to LearnerID is not named LearnerID but txtLearnerID. So you must change the three lines beginning with

    Me.LearnerID = ...

    to

    Me.txtLearnerID = ...

    Note: no value is computed for txtLearnerID if the user selects Agency or Staff from the Classification combo box. Is that by intention?

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: generating ID numbers (Access03)

    Boy that was stupid. Didn't even think of that. That fixed it. The only question I have is this. Why when you tabbed through the LearnerID box would the number appear even though it had the wrong name?
    Agency and staff are given a number by payroll.
    Thank you.

    Fay

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

    Re: generating ID numbers (Access03)

    Apparently tabbing through the txtLearnerID box forces Access to read the value from the LearnerID field...

Posting Permissions

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