ACC: How to Create a Multiuser Custom Counter (95/97) (140908)

SUMMARY

You can define a field as a data type AutoNumber. 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 determine the value of the number assigned to each new record, you may want to use a custom counter. For example, you may want to use a counter that decrements or use a counter that steps according to some value. This article demonstrates a sample user-defined Visual Basic for Applications function that you can use to create a custom counter field.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

First, this article gives you an outline of how to create a custom counter. Then, it gives you a detailed description of how to do this.

First, 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 in this one record.
Next, from within Visual Basic for Applications, open this counter table and retrieve the value stored there.
Next, increment the value retrieved and store the number back into the counter table.
Finally, close the counter table and use the value in an appropriate table as the next available counter.

In a single-user environment, you can accomplish the task described above with macro actions. In a multiuser environment, however, you need 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 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 the function is called.

Creating the Table

On the Insert menu, click Table.
In the New Table dialog box, click Design View, and then click OK.
Add a field to the table called Next Available Counter. Set the data type of the Next Available Counter field to Number. Leave the default Field Size as Long Integer.
On the Edit menu, click PrimaryKey to make the Next Available Counter field the primary key.
On the View menu, click Datasheet view.
Note that a dialog box prompts you to save the table. Save it as Counter Table.
In Datasheet view, type a value of 10 in the Next Available Counter field.
Close the table.

Creating the Module

On the Insert menu, click Module.

Add the following function to the module:

      Function Next_Custom_Counter ()
         On Error GoTo Next_Custom_Counter_Err

         Dim MyDB As Database
         Dim MyTable As Recordset
         Dim NextCounter As Long

         '=================================================================
         'Open table and get the current value of "Next Available Number,"
         'increment the value by 10, and save the value back into the table
         '=================================================================

          Set MyDB = CurrentDb
          Set MyTable = MyDB.OpenRecordset("Counter Table")

          MyTable.Edit
          NextCounter = MyTable("Next Available Counter")

         '=================================================================
         'The next line can be changed to conform to your custom counter
         'preferences. This example increments the value by 10 each time.
         '=================================================================

          MyTable("Next Available Counter") = NextCounter + 10
          MyTable.Update

          MsgBox "Next available counter value is " & Str$(NextCounter)
          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

Save the module as "Custom Counter Demo."

Creating the Form

On the Insert menu, click Form.
In the New Form dialog box, click Design view, and then click OK.
Add a command button to the form.
Set the button's OnClick property as follows:
=Next_Custom_Counter()
Save the form as "Custom Counter Demo."

Running the Function

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