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

ID: Q140908

The information in this article applies to:

Microsoft Access version 7.0, 97

SUMMARY

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

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

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 called Next Available Counter. Set the

   data type of the Next Available Counter field to Number. Leave the
   default Field Size as Long Integer.

4. On the Edit menu, click PrimaryKey to make the Next Available Counter

   field the primary key.

5. On the View menu, click Datasheet view.

6. Note that a dialog box prompts you to save the table. Save it as

   Counter Table.

7. In Datasheet view, type a value of 10 in the Next Available Counter

   field.

8. Close the table.

Creating the Module

1. On the Insert menu, click Module.

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

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 button's OnClick property as follows:

      =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, displaying

   the value of the next counter.

REFERENCES

For more information about creating AutoNumber fields, search the Help Index for "AutoNumber fields, creating," or ask the Microsoft Access 97 Office Assistant.

Additional query words:

Keywords          : kbusage
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 20, 1998