ACC2000: How to Increment the Numeric Portion of a String

This article provides sample 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 and that there is at least one record already in the table.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

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

1. Open an existing database or create a new one.
2. Create a new table in Design view.
3. Add a field called BookID with a Text data type. On the Edit menu, click Primary Key.
4. Add a second field of any data type.
5. Save the table as Increment.
6. Switch to Datasheet view and enter the following records:

      BookID       Second Field
      -------------------------
      BO-110
      BO-111
      BO-112
   					

7. Close the table.
8. Insert a new module, and then type the following code:

   Function FindMax()
   
      Dim db As DAO.Database
      Dim mx As Integer
      Dim rs As DAO.Recordset
      Dim rsVal As String
   
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("Increment", dbOpenDynaset)
   
      rs.MoveFirst
   
      rsVal = rs.Fields("[BookID]").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("[BookID]").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
   					

9. Save the module as modFind_Maximum.
10. On the File menu, click Close and Return to Microsoft Access.
11. Create a new form in Design view based on the Increment table.
12. If it is not displayed, click Field List on the View menu.
13. Drag the BookID field and the second field from the field list to the form.
14. Verify that the DefaultView property of the form is set to Single Form.
15. Click the BookID text box.
16. Set the DefaultValue property of the BookID text box to the following code:

       =FindMax()
    					

17. View the form in Form view and enter a new record. Note that the BookID text box increments to the next available number automatically.

Note that this example works correctly when the DefaultView property of the form is set to 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, 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.

NOTE: This code sample may not maintain the same format. For example, if the highest BookID is BO-006 the next BookID would be BO-7.