How do I create a customized unique, sequential, and automatic numbering system?

[Alan Stump]

Hi folks,

Perhaps somebody out there can help me with the following
issue:

I am a relatively novice, low-impact user of Access who is
trying to create a database of land parcels that my
company has considered purchasing in the past. This is a
simple enough task, but I am hung up on how to create a
custom, sequential, and fully automatic numbering system
(like an autonumbered primary key, I suppose).

Essentially, I want the numbering system to be composed of
a two-letter county code followed by a four-digit number.
For instance, the fourth piece of land we looked at in
Orange County would be designated as OR-0004, the 81st
deal we looked at in Lake county would be LA-0081, the
23rd deal in Sumter would be SU-0023, and so on.

The key here is that the numbering system will be unique,
sequential, and completely automatic. For instance, if I
tell the database that a deal is in Orange County, I want
it to automatically recognize that it is the 73rd piece of
land in Orange county that I am entering into the system,
and automatically assign a number of OR-0073. The next
piece of land in Orange county that I enter in, I want the
system to automatically give it the number OR-0074,
without any prompting from me.

The system should be as automatic, user-friendly, and
error-proof as possible. Can somebody out there please
give me some hints on how I can do this?

Many thanks in advance,
Alan

 

[John Vinson]

Essentially, I want the numbering system to be composed of
a two-letter county code followed by a four-digit number.
For instance, the fourth piece of land we looked at in
Orange County would be designated as OR-0004, the 81st
deal we looked at in Lake county would be LA-0081, the
23rd deal in Sumter would be SU-0023, and so on.

This can be quite simple, or (depending on the situation) a bit more
complex: the difference is the number of simultaneous users who might
want to assign a number. If it's a single-user database, not
networked, or if only one person enters data, try the following:

Have your ID consisting of TWO fields - the County and an Integer
field, let's call it SEQ. In table design view you can ctrl-click both
fields and make them a joint Primary Key; you can then concatenate
them for display purposes.

You'll need to use a Form to do your data entry (table datasheets
don't have any useful events for the purpose). Use a Combo Box
cboCounty on this Form to select the county, and have a textbox txtSEQ
bound to the SEQ field. In the combo's AfterUpdate event put the
following code:

Private Sub cboCounty_AfterUpdate()
Me!txtSEQ = NZ(DMax("[SEQ]", "[yourtablename]", _
"[County] = '" & cboCounty & "'")) + 1
End Sub

This will find the maximum value of SEQ for the selected county; if
this is a county you haven't used before the result will be NULL. The
NZ function will return either 0 if this is the case, or the maximum
value if there is one; the +1 then just adds one to the highest
existing number and stores the result in txtSEQ.

Post back if you have multiple users - the above isn't reliable in
that case, since you might have two users attempting to get a new ID
for Lost Angels at the same time.