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

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" wrote:

> 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

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

"Klatuu" wrote:

> 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" wrote:
>
> > 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