Increment Access form field by 1 plus add an additional field ID .

[JBalkan]

I hope you can help me. I am using Access 2000. I am trying to assign a
number to a record in a form that starts with GO1, increments by 1 and then
ends with a CountyID. The CountyID is a field on the form that the user will
fill out.

For example:

Record 1 would be: G01234K. "K" is the CountyID.

Record 2 might be: G01235C "C" is the CountyID for this record.

Record 3 might be: G01236K

Record 4 might be: G01237A

The CountyID depends upon the user's input.

I went to Office Online and found a very helpful page called "incrementing
the numeric portion of a string in Access." It gave me the exact code to
write in the AfterUpdate event procedure.

Dim strMax As String
strMax = DMax("fldCount", "tblCount")
Me!HiddenCtl = "Cust-" & Right(strMax, Len(strMax) - InStr(1, strMax,
"-")) + 1

I followed the instructions and it worked for incrementing the record.
Unfortunately, I don't understand programming and can't figure out where to
put the [CountyID] field in the above string.

 

[Graham Mandeno]

The CountyID should be stored in a separate field. Otherwise it will be
very difficult to filter or group records based on county, which I am sure
you will want to do in the future, if not now

If the "G" is common to all records, then don't even store it in the table.

This leaves your ID key field as a simple number. You can use an Autonumber
field or, if you wany to be careful to avoid gaps, you can use a long
integer field and manually increment the last number to generate the next ID
value.

The "record number" such as "G01235C" is simply something the user sees - it
doesn't need to exist in the table. Create a query including all the fields
from your base table and add an extra calculated field:
RecordNumber: Format([RecordID], "\G00000") & [CountyID]

You can then base your forms/reports on this query and display the
RecordNumber value as required.