ACC2000: How to Create a Multiuser Custom Counter

When you define a field as an AutoNumber field, Microsoft Access automatically increments the value of the field whenever a new record is added to the table. You have no control over this value.

If you want to control the value of the number assigned to each new record, you can use a custom counter. For example, you can use a counter that decrements or steps according to some value.

This article shows you how to create user-defined Visual Basic for Applications function to create a custom counter field.

Creating a custom counter field requires the following general tasks:

• You create a separate table that will maintain the next available custom counter. This table will have one field and one record, with the value of the next available counter stored in this record.
• You use Visual Basic for Applications to open this counter table and to retrieve the value stored there.
• You increment the value retrieved and store the number back into the counter table.
• You close the counter table, and use the value in an appropriate table as the next available counter.

In a singleuser environment, you can accomplish these tasks with macro actions. In a multiuser environment, however, you must use Visual Basic for Applications to handle the situation where the counter table is locked by another user. Macro actions do not provide the error trapping that is needed for lock handling in a multiuser environment.

The following sets of steps describe in detail how to create a custom-counter function that returns an integer that increments by 10 each time that the function is called.

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.

Creating the Table

1. On the Insert menu, click Table.
2. In the New Table dialog box, click Design View, and then click OK.
3. Add a field to the table named NextAvailableCounter, and set the data type of this field to Number.
4. On the Edit menu, click Primary Key to make the NextAvailableCounter field the primary key.
5. On the View menu, click Datasheet View.
6. Click Yes when you are prompted to save the table, and save the table with the name of CounterTable.
7. In Datasheet view, type a value of 10 in the NextAvailableCounter field, and then close the table.

Creating the Module

1. On the Insert menu, click Module.
2. Add the following function to the module:

   Option Compare Database
   Option Explicit
   
   Function Next_Custom_Counter()
       ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       'For this code to work, it is required that you reference the:
       '   Microsoft ActiveX Data Objects 2.x Library
       'To reference this library, go to the Tools menu, click
       'References, and select the library from the list of available
       'references (version 2.1 or higher).
       ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       On Error GoTo Next_Custom_Counter_Err
   
       Dim rs As ADODB.Recordset
       Dim NextCounter As Long
   
       Set rs = New ADODB.Recordset
   
       'Open the ADO recordset.
       rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
       'Get the next counter.
       NextCounter = rs!NextAvailableCounter
   
       ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       'Open table and get the current value of NextAvailableNumber,
       'increment the value by 10, and save the value back into the table
       '
       'The next line can be changed to conform to your custom counter
       'preferences. This example increments the value by 10 each time.
       ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       rs!NextAvailableCounter = NextCounter + 10
       NextCounter = rs!NextAvailableCounter
       rs.Update
   
       MsgBox "Next available counter value is " & Str(NextCounter)
   
       rs.Close
   
       Set rs = Nothing
   
       Next_Custom_Counter = NextCounter
   
       Exit Function
   
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       'The following error routine should be replaced with a custom
       'error routine. This example only resumes execution when an error
       'occurs. If a record locking error occurs this is fine; however,
       'any non-record locking error will result in an infinite loop.
       '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Next_Custom_Counter_Err:
       MsgBox "Error " & Err & ": " & Error$
       If Err <> 0 Then Resume
       End
   End Function
   					

3. Save the module as Custom Counter Demo.

Creating the Form

1. On the Insert menu, click Form.
2. In the New Form dialog box, click Design View, and then click OK.
3. Add a command button to the form.
4. Set the OnClick property of the command button to the following event procedure:

   =Next_Custom_Counter()
   					

5. Save the form as Custom Counter Demo.

Running the Function

1. Switch the Custom Counter Demo form to Form view.
2. Click the command button. Note that a message box appears that displays the value of the next counter.

For more information about creating AutoNumber fields, click Microsoft Access Help on the Help menu, type autonumber fields, creating in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.