ACC97: How to Automatically Increment a Text Field That Contains Only Numeric Values (306128)

MORE INFORMATION

1. Create a query that returns the maximum value in the text field:
   1. In the database window, click the Queries tab, and then click New.
   2. In the New Query dialog box, click Design View, and then click OK.
   3. In the Show Table dialog box, click the name of the table that contains the text field, click Add, and then click Close.
   4. In the query design grid, type the following expression, where FieldName is the name of the text field:

      MaxValue: Val([FieldName])

   5. On the View menu, click Totals.
   6. In the Total line below the MaxValue field, click Max in the drop-down list.
   7. Save the query as qryMaxVal, and then close it.
2. Open the appropriate form in Design view.
3. On the View menu, click Properties.
4. In the properties window, click the Event tab, click the OnCurrent event, and then click the Build button to the right.
5. In the Choose Builder dialog box, click Code Builder, and then click OK.
6. Type the following code:

   Private Sub Form_Current()
   
      If Me.NewRecord Then
        ' Note that <FieldName> is the name of your text field on your form.
        Me!<FieldName> = DLookup("[MaxValue]", "qryMaxVal") + 1  
      End If
   
   End Sub
   					

7. Save the form.
8. Open the form in Form view, and then move to a new record. Note that the text field automatically increments.