Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 6.7 Create and Use Flexible AutoNumber Fields

6.7.1 Problem

You use
AutoNumber fields in your tables to ensure that you have unique
values for your key fields, but a key based on an auto-incrementing
Long Integer AutoNumber field doesn't sort your
tables in a useful order. Also, auto-incrementing AutoNumber fields
always start at 1, and you want your AutoNumber values to start at
another number. How can you create a replacement for
Access's AutoNumber fields that gets around these
limitations?

6.7.2 Solution

Access makes it easy to add unique value key fields to a table using
the AutoNumber data type (referred to as the Counter data type prior
to Access 95). AutoNumbers are automatically maintained by Access and
ensure a unique value for each record. Auto-incrementing AutoNumber
fields always start at 1, with 1 added for each new record. If your
only concern is changing the starting number, you can do that by
using an append query to insert a record with a specific value in the
AutoNumber field. The next record added will automatically be
assigned that value plus 1. However, you may have other good reasons
for wanting to create a replacement for the built-in AutoNumbers.
This solution shows how to create your own flexible AutoNumber fields
that are multiuser-ready. You can also combine these custom
AutoNumber values with other fields in the table to make your data
sort more intuitively.

Open and run the frmFlexAutoNum form from

06-07.MDB . Add a new record. Type in some data,
and be sure to put a value in the LastName field. Save the new record
by pressing Shift-Enter. When you save the record, a new
auto-incremented value will be placed into the ContactID field (see
Figure 6-10).

Figure 6-10. The frmFlexAutoNum sample form

You can add this functionality to your own applications by following
these steps:

1. Import the tblFlexAutoNum table and the basFlexAutoNum module into
   your database.

2. Prepare your table by adding a new field to become the key value. If
   you want to store a numeric AutoNumber value, set the
   field's type to Number, Long Integer. If you want to
   add more information for sorting, set the new
   field's type to Text and set its length long enough
   to accommodate the numbers returned by the flexible AutoNumber
   routine plus the number of characters you want to concatenate to the
   field.

3. Open the tblFlexAutoNum table and edit the CounterValue field to
   start at the desired value.

4. Open the data-entry form for your
   application in design view. In the form's
   BeforeUpdate event procedure, add code that calls the
   

   acbGetCounter function, writing the returned
   value to your key field. The following code shows a BeforeUpdate
   event procedure that includes a call to the
   

   abcGetCounter function:

   Private Sub Form_BeforeUpdate(Cancel As Integer)
   ' Try to get a unique counter and write it
   ' to the Contact ID field.
   Dim lngCounter As Long
   If IsNull(Me.txtContactID) Then
   lngCounter = acbGetCounter( )
   ' If no counter is available...
   If lngCounter < 1 Then
   ' cancel the Update event.
   Cancel = True
   Else
   ' Write the key field.
   Me.txtContactID = Left(Me.txtLastName, 5) & lngCounter
   End If
   End If
   End Sub

   This code will run whenever a new record
   is added to the form, before the new record is actually written to
   the form's table. The
   lngCounter variable is assigned to the
   value returned by

   acbGetCounter . If the value is
   greater than zero, it is written to the KeyField field. If you want
   to add information to the key field, use the same technique but
   concatenate the AutoNumber value with a value from another field, as
   shown here:

   Dim lngCounter As Long
   lngCounter = acbGetCounter( )
   If lngCounter > 0 Then
   Me.KeyField = Left$(Me.LastName,5) & lngCounter
   End If

   If you are basing your key value on another field, your code should
   ensure that a value exists in that field before attempting to use it.
   The best way to ensure this is to set the Required property of the
   field to Yes.

6.7.3 Discussion

The heart of this technique is the

acbGetCounter
function. This function tries to open the tblFlexAutoNum table
exclusively and, if it succeeds, gets the value in the CounterValue
field and increments the stored value by some fixed number. The
retrieved value is then returned to the calling procedure.

acbGetCounter is shown here:

Public Function acbGetCounter( ) As Long
' Get a value from the counters table and
' increment it
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim blnLocked As Boolean
Dim intRetries As Integer
Dim lngTime As Long
Dim lngCnt As Long
Dim lngCOunter As Long
' Set number of retries
Const conMaxRetries = 5
Const conMinDelay = 1
Const conMaxDelay = 10
On Error GoTo HandleErr
Set db = CurrentDb( )
blnLocked = False
Do While True
For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number = 0 Then
blnLocked = True
Exit For
Else
lngTime = intRetries ^ 2 * _
Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
For lngCnt = 1 To lngTime
DoEvents
Next lngCnt
End If
Next intRetries
On Error GoTo HandleErr
If Not blnLocked Then
If MsgBox("Could not get a counter: Try again?", _
vbQuestion + vbYesNo) = vbYes Then
intRetries = 0
Else
Exit Do
End If
Else
Exit Do
End If
Loop
If blnLocked Then
lngCOunter = rst("CounterValue")
acbGetCounter = lngCOunter
rst.Edit
rst("CounterValue") = lngCOunter + 1
rst.Update
rst.Close
Else
acbGetCounter = -1
End If
Set rst = Nothing
Set db = Nothing
ExitHere:
Exit Function
HandleErr:
MsgBox Err.Number & ": " & Err.Description, , "acbGetCounter"
Resume ExitHere
End Function

After declaring several variables,

acbGetCounter attempts to open a Recordset
object on the tblFlexAutoNum table. By specifying the
dbDenyRead and dbDenyWrite
constants as the Options argument to the
OpenRecordset method, it attempts to lock the table exclusively,
preventing other users from reading or writing to the table. You can
use the dbDenyRead and
dbDenyWrite options only with table-type
recordsets, so if the table is in an external database
you'll need to open the recordset using
OpenDatabase, as shown earlier in this chapter.

The function attempts to obtain a lock
on the acbcAutoNumTable by using a common multiuser coding construct:
a retry loop. The retry loop from

acbGetCounter
is shown here:

For intRetries = 0 To conMaxRetries
On Error Resume Next
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number = 0 Then
blnLocked = True
Exit For
Else
lngTime = intRetries ^ 2 * _
Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
For lngCnt = 1 To lngTime
DoEvents
Next lngCnt
End If
Next intRetries

Note
what happens if the lock is not immediately obtained. The procedure
calculates a long number based on the number of retries, the
acbcMaxDelay and acbcMinDelay
constants that were set at the beginning of the function, and a
random number. This calculated number, lngTime, is then used to waste
time using a For...Next loop that simply counts
from 1 to lngTime. We placed a DoEvents statement
inside the loop so that Access will process any screen activity
during this dead time.

The retry loop and the time-wasting code force the function to pause
briefly before attempting to obtain the lock again. Because this
function is meant to work in a multiuser situation,
it's important that retries are not repeatedly
attempted without waiting for the lock to be released.

acbGetCounter includes a random component to
lngTime that gets larger with each retry to separate out multiple
users who might be trying to obtain the lock at the same time.

If the function cannot lock the table after the number of retries
specified by the acbcMaxRetries constant, it
displays a message box allowing the user to retry or cancel. If the
user chooses to cancel, a value of -1 is returned; if the user
chooses to retry, the whole retry loop is restarted. If the lock
succeeds, the value of the AutoNumber field is saved and the
AutoNumber field is incremented by the value of the
acbcAutoNumInc constant.

The tblFlexAutoNum table provides
AutoNumber values for one table only. You may wish to extend this
technique so that there is some provision for recording multiple
AutoNumber values in tblFlexAutoNum. Alternately, you could create a
separate AutoNumber table for each flexible AutoNumber value you need
in your application. You can hide these tables in the database
container either by prefixing the table names with
"USys" or by checking the Hidden
setting in the tables' properties sheets.

The example form concatenates the first five letters from the
LastName field with the AutoNumber value. Although this convention
can be helpful in sorting, it can also have a negative side effect:
the AutoNumber field will have to be changed when the LastName field
is changed. We included this functionality simply as an example of
the kind of flexibility you have with this technique. In general,
it's not good practice to combine multiple pieces of
information in one field.

If you want to create AutoNumber values in two different copies of a
database that could then be merged together at a later time, you
could use a site-specific alphanumeric prefix to your AutoNumber
field. Since each copy of the database would use a different site
prefix, you wouldn't have duplicate values. However,
you could also accomplish this goal by using a composite primary key
comprised of two fieldsthe AutoNumber and the site ID.

Unlike Access AutoNumbers, the custom
AutoNumbers in this solution are retrieved only when the record is
about to be saved, in the BeforeUpdate event. If a user starts
editing a new record and then cancels, no AutoNumber will be
"wasted" on the canceled record.
This technique therefore is useful in situations in which you need
your numbers to be consecutive, such as for invoice or purchase-order
numbers.