ACC: How to Increment the Numeric Portion of a String (88169)

MORE INFORMATION

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 database.
2. Create a new table in Design view.
3. 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.
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:

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

7. Close the table.
8. 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
   					

9. Close and save the module as modFind_Maximum.
10. Create a new form in Design view based on the Increment table.
11. If it is not displayed, click Field List on the View menu.
12. Drag the Book ID field and the second field from the field list to the form.
13. Verify that the form's DefaultView property is set to Single Form.
14. Select the Book ID control.
15. Set the DefaultValue property of the Book ID text box to the following code:

       =FindMax()
    					

16. 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).