Access Cookbook - Ken Getz [132]
See Also
For more information on working with properties, see Recipe 7.9 in Chapter 7.
6.7. Create and Use Flexible AutoNumber Fields
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?
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:
Import the tblFlexAutoNum table and the basFlexAutoNum module into your database.
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.
Open the tblFlexAutoNum table and edit the CounterValue field to start at the desired value.
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