Microsoft KB Archive/88169

= ACC: How to Increment the Numeric Portion of a String =

Article ID: 88169

Article Last Modified on 11/17/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q88169



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: <pre class="fixed_text">  Book ID       Second Field --  BO-110 BO-111 BO-112 </li> Close the table.</li>  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(&quot;Increment&quot;, dbOpenDynaset) ' NOTE: If you are using Access 2.0 the previous line should be  '   Set rs = db.OpenRecordset(&quot;Increment&quot;, DB_OPEN_DYNASET)

rs.MoveFirst

rsVal = rs.Fields(&quot;[book id]&quot;).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(&quot;[book id]&quot;).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 = &quot;BO-&quot; & (mx + 1)

rs.Close db.Close

Set rs = Nothing Set db = Nothing

End Function </li> Close and save the module as modFind_Maximum .</li> Create a new form in Design view based on the Increment table.</li> If it is not displayed, click Field List on the View menu.</li> Drag the Book ID field and the second field from the field list to the form.</li> Verify that the form's DefaultView property is set to Single Form.</li> Select the Book ID control.</li>  Set the DefaultValue property of the Book ID text box to the following code: <pre class="fixed_text">  =FindMax </li> View the form in Form view and enter a new record. Note that the Book ID field increments to the next available number automatically.</li></ol>

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: kbhowto kbusage KB88169

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.