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
figs/acb2_0610.gif

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 fields?the 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.