Incrementing the Numeric Portion of a String in Access 97/2000

Sometimes you need to use a fixed string followed by an incrementing number as a counter field. This might be handy if you have several fields that contain numeric data and you want an easy way to tell which data you are looking at without checking the field name. For example, you can have a CustomerID field that contains the value Cust-1 and increment the value of this field in subsequent records to Cust-2, Cust-3, and so forth. 

In Microsoft Access, you can increment the numeric portion of the counter field by using an event procedure. The following steps demonstrate this technique by creating a table and a form, inserting text boxes, and entering an event procedure. The third text box control prevents a timing conflict between the event that updates the counter field and the updating of the record.

Create a table with data

1. In the Database window, click Tables under Objects, and then click New.
2. In the New Table dialog box, click Design View, and then click OK.
3. Set the first field name to fldCount and the second field name to Name.
4. Leave the data type set to Text for both fields.
5. Save the table with the name tblCount and open it in Datasheet view. Do not set a primary key for your table.
6. Enter Cust-1 in the fldCount column and Nancy Davolio in the Name column.
7. Close the table.

Create a form and enter the event procedure

1. In the Database window, click Forms under Objects, and then click New.
2. In the New Form dialog box, click AutoForm: Columnar, click tblCount in the Choose the table or query where the object's data comes from box, and then click OK.
3. Switch to Design view.
4. Using the Text Box tool in the toolbox, add an unbound text box to the form.
5. Set the Name property of the new text box to HiddenCtl.
6. Set the ControlSource property for the HiddenCtl box to fldCount.
7. Click the Name box on the form to display the property sheet for the Name box.
8. On the Event tab, click the AfterUpdate box, and then click the Build button (...) next to the box.
9. In the Choose Builder dialog box, click Code Builder, and then click OK.
10. In the AfterUpdate event procedure for the Name field, type the following text between the Sub and End Sub statements:

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

11. Switch to Form view.
12. Use the TAB key to move to the Name box, type Janet Leverling, and then press the TAB key.

The value displayed in the fldCount and HiddenCtl boxes is the string Cust- plus the next incremental value of 2.

Note  The HiddenCtl box is visible in this procedure for demonstration purposes. You may want to hide the box from the user of the form by setting its Visible property to False.