Another numbering question

[The Colonel]

I created a database to log correspondence topics at work. Each entry/issue
is assigned a unique log number. The number is a combination of things.
Example: ADCN07-1

The text letters identify the particular location/office, the "07" would be
the current year, and the last number should be a sequential number,
automatically determined as new entries are made.

I previously used an autonumber field for this, and simply adjusted the form
formatting to provide the proper display. However, several problems exist.

1. Getting an error when attempting to use 2-digit year DatePart (Access
2003).
2. Need autonumber to reset to "1" each year (January 1).
3. Need log # to store as displayed for search purposes. Thus the year needs
to store and not fluctuate.

For 2007, the first entry would be ADCN07-1. Come 2008, the first entry
would be ADCN08-1. In this way past entries can still be identified.

 

[Pat Hartman]

Past entries can always be identified as long as you keep a create date. I
know that having primary keys with "meaning" provides a certain comfort
level but you can accomplish the same thing without actually using the
generated value as a PK. You can still search by year (or month or date)
because you store create date as a separate field. You can still search by
sequence number because you store sequence number as a separate field. You
can still search by location because you store location as a separate field.
You can always create the "meaningful" value on the fly as necessary.

To extract a two digit year from a date field, use Format() --
Format(CreateDate, "yy")

To find the next value for the sequence number, use DMax() --
Me.SeqNum = Nz(DMax("SeqNum","YourTable","Year(CreateYear) = " &
Year(Date()) & " AND Location = '" & Me.Location & "'"),0) +1

To display the calculated "key"
Me.CalcKey = Me.Location & Me.Format(CreateDate, "yy") & "-" & Me.SeqNum

 

[The Colonel]

What if I create a text field as the primary key in my main issues table. I
could incorporate the formatting and programming into VBA code to adjust how
the reference number appears. That number (including appropriate format)
would then be stored in the unique text field. I have another field called
"Date Received". It defaults to today's date. That would be appropriate for
retreiving the year. How is resetting the numbering value to "1" come each
new year handled?

 

[Pat Hartman]

Numbers that you need to do arithmetic with should be stored as numbers.
Formatting is a display issue and should have no impact on how data is
stored. I gave you the code to generate the next sequence number. If you
examine it, you will see that it will generate a 1 when there are no
existing numbers for the given location and year. That handles the year
change without issue. If you only want to maintain a single sequence for
the year without regard to location, just remove location from the DMax()'s
criteria.

 

[The Colonel]

I eliminated the location. The same location is used throughout the database
based on the initial setup info (organization info). I have a table called
tblIssues. I created a field called SeqNum (long integer) to store the
numerical sequence numbers. I have a field called DateReceived which is used
to log the date the issue is documented. This is my source instead of
CreateDate. I used the following code in the On Current event procedure of a
form called frmIssues.

Me.CalcKey = subfrmPrefix.Form!Prefix & Format(Me.DateReceived, "yy") & "-"
& Me.SeqNum

Given the above data, I modified the other code to generate the appropriate
sequence number.

Me.SeqNum = Nz(DMax("SeqNum", "tblIssues", "Year(DateReceived) = '" &
Year(Date) & "'"), 0) + 1

Where should I be running this code from? I tried it in the On Current event
procedure of the form, but it keeps increasing the existing sequence numbers
in addition to adding new ones. Not sure what I'm doing wrong. Any help is
appreciated.

 

[Pat Hartman]

The code should go into the BeforeInsert event. That way it only runs for
new records. Alternatively, it can go into the BeforeUpdate event but in
that case, you'll need to put it inside an If that determines if this is a
current record.