generating ID numbers (Access03)

Topic

New Reply

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

Reply | Quote

Replies

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 "Left(LearnerID,1)='C'" ?

Reply | Quote

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

Reply | Quote

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 for instructions.

Reply | Quote

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

Reply | Quote

Yes, please.

Reply | Quote

Sorry. Here is the database. Thank you. Fay

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 )

Reply | Quote