ACC: How to Create a Multiuser Custom Counter (1.x/2.0) (88159)

SUMMARY

You can define a field as a counter in a Microsoft Access table. When you define a field as a counter, Microsoft Access manages the numbering in the field. When you add a record, Microsoft Access assigns the next available value to the field. You have no control over this value.

You may want to use a unique counter that is not immediately sequential in nature. For example, you may want to use a counter that decrements, or a counter that steps according to some value.

This article demonstrates a sample user-defined Access Basic function that you can use to create a custom counter field.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.

MORE INFORMATION

The following steps outline in general how to create a custom counter. Later in the article, you will find a more detailed description of how to create a custom counter.

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.
From within Access Basic, open this counter table and retrieve the value stored there.
Increment the value retrieved and store the number back into the database.
Close the table and use the value in the appropriate table as the next available counter.

In a single-user environment, you can accomplish the steps described above with macro actions. In a multiuser environment, you need Access Basic to handle the event where this counter table is locked. Macro actions do not provide the locking control needed in a multiuser environment.

The following example describes how to create a custom counter field with positive number values divisible by 10 (that is, 10, 20, 30, and so on) in sequential order:

The Table

From the File menu, choose New, and then select Table.
Add a field to the table called Next Available Counter. Set the data type of the Next Available Counter field to Number.
From the Edit menu, choose Primary Key to make the Next Available Counter field the primary key.
From the View menu, choose Datasheet View.
A dialog box appears, prompting you to save the table. Save the table as Counter Table.
In Datasheet view, enter a value of 10 in the Next Available Counter field.
From the File menu, choose Close to close the table.

The Module

From the File menu, choose New, and then select Module to create a new module.

Type the following function in the module:

       Function Next_Custom_Counter ()

          On Error GoTo Next_Custom_Counter_Err

          Dim MyDB As Database
          Dim MyTable As Table

          Dim NextCounter As Integer
          ' If the control is large, you may need to make this a Double.

          '==============================================================
          ' Open table and get the next available number, increment value
          '    by 10 and save the number back into the table.
          '==============================================================

          Set MyDB = CurrentDB()
          Set MyTable = MyDB.OpenTable("Counter Table")

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

          '===============================================================
          ' The next line can be changed to conform to your custom counter
          ' preferences. This example only 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 if an error
   ' occurs. If a record locking error occurs, this is fine. But 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

On the File menu, choose Save. Save the module as Custom Counter Demo.

The Form

From the File menu, choose New, and then select Form. Choose the Blank Form button to start with a blank form.
Add a command button to the form, and set the control's OnPush property to:

=Next_Custom_Counter()

NOTE: In Microsoft Access version 2.0, the OnPush property is called the OnClick property.
Save the form as Custom Counter Demo.

Execution

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