Auto-generate number

[ckrogers]

Hi! I've been reading previous posts and trying to set this up myself, but
keep getting various error messages I don't know how to fix. So, here's what
I'm trying to do:

- The first field that is filled in on a new form is a location ID (field
name "Location) ... which is a three character field. (i.e., "001", "002",
etc.)
- Once they've chosen their location, I need to automatically assign a log
number with the following format: "aaa-bbcc-ddd" where aaa=location ID;
bb=two-digit current year; cc=two-digit current month; and ddd=sequential
number based on the a/b/c values. For example, the first log number for
April 2006 for location "001" would be "001-0604-001", then "001-0604-002",
etc.; the first log number for April 2006 for location "002" would be
"002-0604-001", etc.

I'm fairly new to Access programming, so I need some help with coding the
event procedure to provide this log number ... and I'm not even sure which
event to assign the procedure to?

Thanks for your help!
Cindy

 

[Klatuu]

You can use the DMax function to find out what the current highest number
available is. The code below assumes you are storing the dashes in your
data, bad idea, but commonly done. If you are not, just leave them out of
the code. The names are all made up, so you will have to change them to suit
your needs.

Dim varNextNum as Variant
Dim strLogPrefix as String

strLogPrefix = Me.txtLocation & "-" & Format(Date,"yymm") & "-"
varNextNum = DMax("[LOG_NUMBER]", "LogTable", _
Left([LOG_NUMBER,9) = '" & strLogPrefix & "'")
varNextNum = IIf(IsNull(varNextNum),1, Right(varNextNum,3) + 1)
Me.txtLogNumber = strLogPrefix & Format(varNextNum, "000")

 

[ckrogers]

That worked perfectly. Thank you so much, "Klatuu"!
Cindy

You can use the DMax function to find out what the current highest number
available is. The code below assumes you are storing the dashes in your
data, bad idea, but commonly done. If you are not, just leave them out of
the code. The names are all made up, so you will have to change them to suit
your needs.

Dim varNextNum as Variant
Dim strLogPrefix as String

strLogPrefix = Me.txtLocation & "-" & Format(Date,"yymm") & "-"
varNextNum = DMax("[LOG_NUMBER]", "LogTable", _
Left([LOG_NUMBER,9) = '" & strLogPrefix & "'")
varNextNum = IIf(IsNull(varNextNum),1, Right(varNextNum,3) + 1)
Me.txtLogNumber = strLogPrefix & Format(varNextNum, "000")

Hi! I've been reading previous posts and trying to set this up myself, but
keep getting various error messages I don't know how to fix. So, here's what
I'm trying to do:

- The first field that is filled in on a new form is a location ID (field
name "Location) ... which is a three character field. (i.e., "001", "002",
etc.)
- Once they've chosen their location, I need to automatically assign a log
number with the following format: "aaa-bbcc-ddd" where aaa=location ID;
bb=two-digit current year; cc=two-digit current month; and ddd=sequential
number based on the a/b/c values. For example, the first log number for
April 2006 for location "001" would be "001-0604-001", then "001-0604-002",
etc.; the first log number for April 2006 for location "002" would be
"002-0604-001", etc.

I'm fairly new to Access programming, so I need some help with coding the
event procedure to provide this log number ... and I'm not even sure which
event to assign the procedure to?

Thanks for your help!
Cindy