Microsoft KB Archive/306128

= ACC97: How to Automatically Increment a Text Field That Contains Only Numeric Values =

Article ID: 306128

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q306128



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



SUMMARY
This article demonstrates how to automatically increment a text field that contains only numbers, when you add a new record on a form.



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

MaxValue: Val([ ])

 On the View menu, click Totals.</li> In the Total line below the MaxValue field, click Max in the drop-down list.</li> Save the query as qryMaxVal, and then close it.</li></ol> </li> Open the appropriate form in Design view.</li> On the View menu, click Properties.</li> In the properties window, click the Event tab, click the OnCurrent event, and then click the Build button to the right.</li> In the Choose Builder dialog box, click Code Builder, and then click OK.</li>  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(&quot;[MaxValue]&quot;, &quot;qryMaxVal&quot;) + 1 End If

End Sub </li> Save the form.</li> Open the form in Form view, and then move to a new record. Note that the text field automatically increments.</li></ol>

<div class="references_section">