Unique Number in Table

L

Lamar

I need to identify each record in my table with a unique number which
includes the year. So I want to identify the year (Format(Now(),"yy") but
then I need an Autonumber after the year. What is the syntax? I could not
figure it out?

Thanks for any help.
 
J

John Vinson

I need to identify each record in my table with a unique number which
includes the year. So I want to identify the year (Format(Now(),"yy") but
then I need an Autonumber after the year. What is the syntax? I could not
figure it out?

Thanks for any help.
Click to expand...

I'd suggest using TWO fields, not one - it's much easier to do the
custom counter code. If you wish to use just one field, though, you'll
need code like this in your Form's Beforeinsert event (you MUST use a
Form, tables don't have any usable events). I'll assume you have a
table named MyTable, with a field named MyID, text, looking like
05-00001 for the first record in 2005:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String
strID = DMax("[MyID]", "[MyTable]") ' find biggest existing ID
iMax = Val(Mid(strID, 4) ' extract numeric portion
If Format(Date, "yy") > Left(strID, 2) Then ' new year?
iMax = 0
End If
Me!MyID = Format(Date, "yy-") & Format(iMax + 1, "00000")
End Sub

Needs error checking (e.g. to handle the 100000th record in a year),
etc. but should get you started.

John W. Vinson[MVP]
 
L

Lamar

How do I use two fields for custom code counter?

John Vinson said:
I need to identify each record in my table with a unique number which
includes the year. So I want to identify the year (Format(Now(),"yy") but
then I need an Autonumber after the year. What is the syntax? I could not
figure it out?

Thanks for any help.
Click to expand...

I'd suggest using TWO fields, not one - it's much easier to do the
custom counter code. If you wish to use just one field, though, you'll
need code like this in your Form's Beforeinsert event (you MUST use a
Form, tables don't have any usable events). I'll assume you have a
table named MyTable, with a field named MyID, text, looking like
05-00001 for the first record in 2005:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iMax As Integer
Dim strID As String
strID = DMax("[MyID]", "[MyTable]") ' find biggest existing ID
iMax = Val(Mid(strID, 4) ' extract numeric portion
If Format(Date, "yy") > Left(strID, 2) Then ' new year?
iMax = 0
End If
Me!MyID = Format(Date, "yy-") & Format(iMax + 1, "00000")
End Sub

Needs error checking (e.g. to handle the 100000th record in a year),
etc. but should get you started.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

How do I use two fields for custom code counter?
Click to expand...

Make them a joint Primary Key (by ctrl-clicking them both and
selecting the Key icon); you'ld one text field, default value
Format(Year(Date()), "yy"), and one Integer field.

You'ld use some simpler code in the Form's BeforeInsert (still must
use the form):

Me!txtSeqno = NZ(DMax("[SeqNo]", "[MyTable]", "[IDYr] = '" &
Format(Date, "yy") & "'")) + 1


John W. Vinson[MVP]
 
Top