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

MORE INFORMATION

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

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.