databasedev.co.uk - database solutions and downloads for microsoft access

Automatically Increment a Value in a Database

Microsoft Access contains the AutoNumber data type that will automatically increment a value by 1 when set to New Values: Increment in the properties of the field.

This may not always be what the user of your Microsoft Access database requires and we have detailed an alternative approach to incrementing a fields value by a specific amount. Below shows an example that uses the DMAX function to find the highest value in the table and adds 1 to this value in a new record.

The above image shows the provision of the New ID command button. This button is only available (enabled) when accessing a New record. The On_Click event procedure that runs when this button is clicked can be seen below:

Private Sub cmdNewId_Click()
'On click of button a new Customer ID is generated and
'focus is moved to strLastName field.
    Me![lngId] = NewCustID()
    Me![strLastName].SetFocus
    Me![cmdNewId].Enabled = False
End Sub

This procedure sets the ID value ([lngID]) by accessing the NewCustID() function detailed below. Once the New ID is assigned the focus is moved to the LastName field and the command button is un enabled.

To set the New ID automatically, you should ensure that the cursor is visible in the ID field. The function ran by clicking the command button is shown below:

Public Function NewCustID() As Long

On Error GoTo NextID_Err

Dim lngNextID As Long

    'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
    lngNextID = DMax("[lngID]", "tblPracticeEmployeeData") + 1

    'Assign function the value of the Next ID
    NewCustID = lngNextID

    'Exit function now after successful incrementing or after error message
Exit_NewCustID:
Exit Function

    'If an error occurred, display a message, then go to Exit statement
NextID_Err:
    MsgBox "Error " & Err & ": " & Error$

    Resume Exit_NewCustID

End Function

This uses the DMAX function to find the highest EmployeeID value in table [tblPracticeEmployeeData] and adds 1 to the value.

It is worth mentioning that this method has limitations in a multi user environment. You run the risk of 2 (or more) users getting the same number if they're trying to add records at the same time. If you don't have multiple people entering data, then it won't be a problem.

To view an example of this database in action you can download the Microsoft Access example file available from the Microsoft Access Forms page or the Microsoft Access Downloads section of databasedev.co.uk