-----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
.
