ACC: How to Increment the Numeric Portion of a String

ID: Q88169

The information in this article applies to:

Microsoft Access versions 97, 2.0, 7.0

Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article provides example code that you can use to set the default value of a bound Text field in a form to the next higher numeric value (default value = highest numeric value + 1).

The sample code does the following:

It strips any leading text characters and finds the highest numeric value used in a table.

It adds 1 to the highest numeric value.

It reconnects the leading text characters.

NOTE: This example assumes that the number of leading text characters is known at the time that the form is designed.

MORE INFORMATION

Use the following sample procedure to increase the default value of a bound Text field on a form:

Open an existing database or create a new database.

Create a new table in Design view.

Add a field called Book ID with a Text data type. On the Edit menu, click Primary Key.

NOTE: In version 2.0, click Set Primary Key on the Edit menu.

Add a second field of any data type.

Save the table as Increment.

Switch to Datasheet view and enter the following records:


   Book ID       Second Field
   --------------------------
   BO-110
   BO-111
   BO-112

Close the table.

Insert a new module and copy or type the following code:


Function FindMax()

   Dim db As Database
   Dim mx As Integer
   Dim rs As Recordset
   Dim rsVal As String

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("Increment", dbOpenDynaset)
   ' NOTE: If you are using Access 2.0 the previous line should be
   '   Set rs = db.OpenRecordset("Increment", DB_OPEN_DYNASET)

   rs.MoveFirst

   rsVal = rs.Fields("[book id]").Value
   ' set mx equal to the numeric portion of the field
   mx = Right(rsVal, Len(rsVal) - 3)

   ' loop to make sure you have the maximum number
   Do While Not rs.EOF
      rsVal = rs.Fields("[book id]").Value
      If Right(rsVal, Len(rsVal) - 3) > mx Then
         mx = Right(rsVal, Len(rsVal) - 3)
      End If
      rs.MoveNext
   Loop

   ' increment the maximum value by one and
   ' combine the text with the maximum number
   FindMax = "BO-" & (mx + 1)

   rs.Close
   db.Close

   Set rs = Nothing
   Set db = Nothing

End Function

Close and save the module as modFind_Maximum.

Create a new form in Design view based on the Increment table.

If it is not displayed, click Field List on the View menu.

Drag the Book ID field and the second field from the field list to the form.

Verify that the form's DefaultView property is set to Single Form.

Select the Book ID control.

Set the DefaultValue property of the Book ID text box to the following code:


   =FindMax()

View the form in Form view and enter a new record. Note that the Book ID field increments to the next available number automatically.

Note that this example works correctly when the form's Default View property is Single Form; it may not work correctly when the property is set to Continuous Forms or Datasheet. When you move to a new record and begin to enter data, Microsoft Access displays the next empty record. The default values for this record are calculated before the record that you are currently editing is committed.

If you are working in a multiuser environment, it is possible that more than one user may receive the same calculated Book ID value. Although you can manually change the Book ID, you can also maintain the highest numeric value in a separate table by using a macro or Visual Basic code (or Access Basic code in version 2.0).

Additional query words: custom counter inf

Keywords : kbusage kbdta TblPriky
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto