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