Microsoft KB Archive/88159

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

Article ID: 88159

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q88159



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



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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x, or the &quot;Building Applications&quot; 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.


 * 1) 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.
 * 2) From within Access Basic, open this counter table and retrieve the value stored there.
 * 3) Increment the value retrieved and store the number back into the database.
 * 4) 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

 * 1) From the File menu, choose New, and then select Table.
 * 2) Add a field to the table called Next Available Counter. Set the data type of the Next Available Counter field to Number.
 * 3) From the Edit menu, choose Primary Key to make the Next Available Counter field the primary key.
 * 4) From the View menu, choose Datasheet View.
 * 5) A dialog box appears, prompting you to save the table. Save the table as Counter Table.
 * 6) In Datasheet view, enter a value of 10 in the Next Available Counter field.
 * 7) 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(&quot;Counter Table&quot;)

MyTable.Edit NextCounter = MyTable(&quot;Next Available Counter&quot;)

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

MyTable(&quot;Next Available Counter&quot;) = NextCounter + 10 MyTable.Update

MsgBox &quot;Next available counter value is &quot; & 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 &quot;Error &quot; & err & &quot;: &quot; & error$ If ERR <> 0 then Resume End End Function  On the File menu, choose Save. Save the module as Custom Counter Demo.

The Form

 * 1) From the File menu, choose New, and then select Form. Choose the Blank Form button to start with a blank form.
 * 2) 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.
 * 1) Save the form as Custom Counter Demo.

Execution

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

Additional query words: network duplicate

Keywords: kbhowto kbusage KB88159

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.