How to create automatic, sequential, customized numbering system

V

Van T. Dinh

You are much better to store as 2 SEPARATE Fields.
Remember that the first Normal Rule requires Field values
must be atomic, i.e. storing a single item of data. For
display, you can combine the 2 Filed values into 1 display
String as you wish.

Assume that you use 2 Fields County_Code & SeqNo and set
these 2 Fields as the multi-Field PrimaryKey for your
Table, you can use code something like:

****Untested code****
Private Sub cboCounty_Code_AfterUpdate()
Me![ID] = Nz(DMax("SeqNo", "YourTable", _
"[County_Code] = '" & cboCounty_Code & "'"), 0) + 1
End Sub
****

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi folks,
I have a quick visual basic code question that somebody
may be able to provide some insight on: I am creating a
database used to track land development deals. I want my
records to have a numbering system that is comprised of a
two-letter county code followed by a number (for instance
OR-0001, OR-0002, SE-0043, etc.).

The key 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 recognize that it is the 73rd piece of land in
Orange County that I have entered into the system and
automatically assign it a number of OR-0073. The next
piece of land that I enter in, I want the system to
automatically assign it the number of OR-0074, and so on.

I have a main table, called "Land Database". In addition,
I have created a lookup table for the county code
(called "County Code") that has a relationship with the
county code field in my "Land Database" table (by the way,
there are a total of nine counties that could be
referenced in this database). I also have a lookup table
called "ID" that is simply filled with integers from 1 to
4000. That table is related to the "ID" field in my "Land
Database" table. These two fields "County Code" and "ID"
will combine to form my numbering system.

I wrote the following code in the AfterUpdate event of the
County_Code combo box in my entry form:

Dim ID As Integer
Public County_Code As String

Private Sub cboCounty_Code_AfterUpdate()
Me!ID = Nz(DMax("[ID]", "[ID]", "[County_Code] = '" &
cboCounty_Code & "'"), 0) + 1
End Sub

This is very close to working, but I have the following
problem: In my first record, I entered a county code
of "OR" (for Orange County), and the ID automatically
updates with the number "1" in the "ID" field (as it
should, since it is the first Orange County record in my
database). However, the next time I enter a record from
Orange County, the "ID" field updates with the number "1"
again, when it should register a "2", and then the next
one should read "3", and so on.

Where am I going wrong in my code? How can I correct this
so that the ID field automatically adds 1 to the last
number that was entered in the previous record's ID field?
If it helps, I can send a copy of all of my relationships
and forms to whomever can help me with this.

All apologies for the long post, and many thanks to
anybody who has some insight for me.
All the best,
Alan
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top