ACC2000: How to Increment the Numeric Portion of a String

ID: Q209830

The information in this article applies to:

Microsoft Access 2000

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

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:


   BookID       Second Field
   -------------------------
   BO-110
   BO-111
   BO-112

Close the table.

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

Save the module as modFind_Maximum.

On the File menu, click Close and Return to Microsoft Access.

Create a new form in Design view based on the Increment table.

If it is not displayed, click Field List on the View menu.

Drag the BookID field and the second field from the field list to the form.

Verify that the DefaultView property of the form is set to Single Form.

Click the BookID text box.

Set the DefaultValue property of the BookID text box to the following code:


   =FindMax()

View the form in Form view and enter a new record. Note that the BookID text box increments to the next available number automatically.

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 : kbusage kbdta TblPriky
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto