Autonumbering

[Fay Yocum]

I have the following code in a textbox AfterUpdate event.

Private Sub txtTimeEnd_AfterUpdate()

Dim NewID As String
Dim OldID As String

OldID = Nz(DMax("Right(txtClassNum,3)", "tblSession",
"left(txtClassNum,1)= (txtClassID)"), "000")
NewID = Format(txtClassID) & "-" & Format(Right(OldID, 3) + 1, "000")
Me.txtClassNum = NewID

End Sub

It almost works. It formats almost correctly. For example it gives me 5-001.
But there is already a 5-001 so it isn't adding correctly. I now have three
5-001. What am I missing here?

Also I see another issue. the txtClassID will become greater that a single
digit number. So how should I change the code
"left(txtClassNum,1)= (txtClassID)") when I don't know how many digits will
comprise the txtClassID?

Thank you for your assistance. I hope you have a great week.

Fay

 

[Guest]

Sorry about the double posting. One did not show up on my listing at home for
some reason. Thank you for viewing this message and any answers you may bring
to the table. Fay

 

[John Vinson]

I have the following code in a textbox AfterUpdate event.

Private Sub txtTimeEnd_AfterUpdate()

Dim NewID As String
Dim OldID As String

OldID = Nz(DMax("Right(txtClassNum,3)", "tblSession",
"left(txtClassNum,1)= (txtClassID)"), "000")
NewID = Format(txtClassID) & "-" & Format(Right(OldID, 3) + 1, "000")
Me.txtClassNum = NewID

End Sub

It almost works. It formats almost correctly. For example it gives me 5-001.
But there is already a 5-001 so it isn't adding correctly. I now have three
5-001. What am I missing here?

You can solve both problems by doing arithmatic on numbers rather than
on strings! You're also looking for ClassNums equal to the text string
"txtClassID" rather than the value in the form control.

Try:

Dim OldID As Integer
Dim NewID As String
OldID = NZ(Val(DMax("Right(txtClassNum,3)", "tblSession",
"left(txtClassNum,1)='" & Me!txtClassID & "'")))
NewID = ... <etc>

Note that storing two disparate pieces of information in the same
field is NOT good design. I would suggest using separate fields for
the ClassID and the Session; you can make the two fields a joint
two-field Primary Key if you wish, and concatenate them whenever you
need to see them together.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thank you for your response John.

Ok I understand you comments about bad technique and would like to avoid
that. But what I need, or want, to end up with is a consecutive list of
session numbers for the different class number.

For example two classes
AccuData classID = 1
Assessment ClassID = 2

I don't want 1-001, 2-002, 2-003, 1-004
I want to be able to track 1-001, 1-002 etc and 2-001, 2-002 etc.

Can you point me in the right direction?

 

[John Vinson]

Thank you for your response John.

Ok I understand you comments about bad technique and would like to avoid
that. But what I need, or want, to end up with is a consecutive list of
session numbers for the different class number.

For example two classes
AccuData classID = 1
Assessment ClassID = 2

I don't want 1-001, 2-002, 2-003, 1-004
I want to be able to track 1-001, 1-002 etc and 2-001, 2-002 etc.

Can you point me in the right direction?

You're almost there.

My suggestion is that you abandon the idea of having a single
"intelligent" key field containing both the class number and the
session number.

Instead, use TWO integer fields in your table. Class 2-003 would have
a field ClassID with the value 2, and a Session field with the value
3. Access allows you to define a primary key which consists of up to
ten fields; you can select both these fields in the table design view,
and click the key icon to make them a joint two-field Primary Key.

In order to *SEE* the concatenated key, you could have a textbox with
a Control Source of

[ClassID] & Format([Session], "-000")

Your DMax code now becomes much simpler: In the AfterUpdate event of
the ClassID combo box (surely you're picking classes from a list...)
put code like:

Private Sub cboClassID_AfterUpdate()
If Me.NewRecord Then ' adding a new session?
If IsNull(Me!txtSession) Then ' don't step on existing data
Me!txtSession = NZ(DMax("[Session]", "[tablename]", _
"[ClassID] = " & Me!cboClassID)) + 1
End If
End If
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John I will deal with this at home where I work on this project for
the Hospital. Fay

Thank you for your response John.

Ok I understand you comments about bad technique and would like to avoid
that. But what I need, or want, to end up with is a consecutive list of
session numbers for the different class number.

For example two classes
AccuData classID = 1
Assessment ClassID = 2

I don't want 1-001, 2-002, 2-003, 1-004
I want to be able to track 1-001, 1-002 etc and 2-001, 2-002 etc.

Can you point me in the right direction?

You're almost there.

My suggestion is that you abandon the idea of having a single
"intelligent" key field containing both the class number and the
session number.

Instead, use TWO integer fields in your table. Class 2-003 would have
a field ClassID with the value 2, and a Session field with the value
3. Access allows you to define a primary key which consists of up to
ten fields; you can select both these fields in the table design view,
and click the key icon to make them a joint two-field Primary Key.

In order to *SEE* the concatenated key, you could have a textbox with
a Control Source of

[ClassID] & Format([Session], "-000")

Your DMax code now becomes much simpler: In the AfterUpdate event of
the ClassID combo box (surely you're picking classes from a list...)
put code like:

Private Sub cboClassID_AfterUpdate()
If Me.NewRecord Then ' adding a new session?
If IsNull(Me!txtSession) Then ' don't step on existing data
Me!txtSession = NZ(DMax("[Session]", "[tablename]", _
"[ClassID] = " & Me!cboClassID)) + 1
End If
End If
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps