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

J

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

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.
 

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