afterupdate leaves gaps in primary key

Question

I am developing an access database with some forms that are only used for data entry. The problem is that, when I open any of these forms, access creates a new entry in the underlying table, including incrementing the autonumber primary key of the underlying table, and the autonumber remains incremented even if the user opts not to create the record in the database. This means that there are gaps in the sequence of autonumbers in the actual table due to all the times users open and close the form without committing changes.

I am using the following code in the beforeupdate method to allow users to discard bad data:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Provide the user with the option to save/undo
    'changes made to the record in the form
    If MsgBox("Data has been entered into this form." _
        & vbCrLf & vbCrLf & "Do you want to save this data?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
    Else
        DoCmd.RunCommand acCmdUndo
    End If
End Sub  

I had thought to use the beforeinsert event, but was having problems opening up the form when beforeinsert was used.

Can someone show me how to set things up so that these gaps do not get created in the sequence of autonumber values? This database will be used by perhaps 10 concurrent users.

Answer 1

You should not be assigning any significance to the autonumber field's value. Autonumbers allow you to define unique values for each row, and in doing so they are not guaranteed to be sequential. I strongly suggest you only use the autonumber field to identify unique records, not as some sort of counter or human-readable value.

Now, with that out of the way, if you need each record to really have a sequential value, then what you need to do is

1. Create a separate table that has just one row, one column that holds the next value in the sequence.
2. Whenever a record is saved from the data entry form, you exclusively lock that counter table, preventing any other users from reading or writing to it.
3. Retrieve the current value from the counter table and use that in your new dataentry record.
4. Update the value in the counter table to increment to the next value.

Microsoft provides a function that makes it easy to exclusively lock a table: DAOOpenTableExclusive(), which can be found here.

You can use the code found here to create a counter table.

Answer 2

If you have the Record Source for a form set, the entry of any bound control will cause the any AutoNumber field to be populated. If the user abandons or doesn't enter all the required information, then that auto number is skipped.

To avoid this you could do the saving of the record yourself. Lets say you have the following table defined:

And the following form created:

With the following code behind:

Private Sub cmdClose_Click()
  DoCmd.Close
End Sub


Private Sub cmdSave_Click()
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO z_TestTable (DueDate, " & _
                                        "DateReceived, " & _
                                        "Terms, " & _
                                        "ECOFee, " & _
                                        "Classification) " & _
                               "VALUES (#" & txtDueDate & "#, " & _
                                       "#" & txtDateReceived & "#, " & _
                                       "'" & txtTerms & "', " & _
                                       txtECOFee & ", " & _
                                       "'" & txtClassification & "')"
  MsgBox ("RECORD SAVED")
End Sub

Private Sub txtClassification_AfterUpdate()
  funCheckForRequiredFields
End Sub


Private Sub txtDateReceived_AfterUpdate()
  funCheckForRequiredFields
End Sub


Private Sub txtDueDate_AfterUpdate()
  funCheckForRequiredFields
End Sub


Private Sub txtECOFee_AfterUpdate()
  funCheckForRequiredFields
End Sub


Private Sub txtTerms_AfterUpdate()
  funCheckForRequiredFields
End Sub


Function funCheckForRequiredFields()
  Dim NotComplete As Boolean
  NotComplete = False

  If (IsNull(txtDueDate)) Then NotComplete = True
  If (IsNull(txtDateReceived)) Then NotComplete = True
  If (IsNull(txtECOFee)) Then NotComplete = True
  If (IsNull(txtClassification)) Then NotComplete = True
  If (IsNull(txtTerms)) Then NotComplete = True

  If (NotComplete) Then _
    cmdSave.Enabled = False Else _
    cmdSave.Enabled = True
End Function

The form opens with the Save Record button disabled. The Save Record button is only enabled after all fields are entered. When the Save Record is pressed the record is saved via the DoCmd.RunSQL command.

I set the format of the controls on the form to correspond with the data required. This insures that the data entered is checked for validity.

By using this method the only way that an AutoNumber field will be used and not have had a record saved against it is if the INSERT SQL is invalid. Which should never happen since it is being checked beforehand.

Answer 3

You can use the max function to determine the maximum value from the column in the table and just add one just before saving it.

EDIT: To let this work in a multiuser environment, set the fields 'Indexed to' Yes (No Duplicates).