Microsoft KB Archive/209830

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

Article ID: 209830

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209830



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



MORE INFORMATION
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:  Open an existing database or create a new one. Create a new table in Design view. Add a field called BookID with a Text data type. On the Edit menu, click Primary Key. 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">  BookID       Second Field -  BO-110 BO-111 BO-112 </li> Close the table.</li>  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 </li> Save the module as modFind_Maximum .</li> On the File menu, click Close and Return to Microsoft Access.</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 BookID field and the second field from the field list to the form.</li> Verify that the DefaultView property of the form is set to Single Form.</li> Click the BookID text box.</li>  Set the DefaultValue property of the BookID 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 BookID text box increments to the next available number automatically.</li></ol>

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.

Additional query words: inf custom counter

Keywords: kbhowto kbusage KB209830

-

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

© Microsoft Corporation. All rights reserved.