Instead of using an auto-number in a database, you can assign
numbers yourself with VBA code.
There are several advantages to this:
* You can start at any number you want and you can skip over a set of numbers.
* Adding new records to a table with DAO or ADO in VBA code can cause timing issues in the
adding of auto-numbers. This is avoided with this function.
* If you are importing records from an external source, you can control how they are numbered.
* This module can be reused for any application that uses VBA code, such as Access, Excel and VB.
To use this function, create a table called "tblDefaults".
This is where the starting numbers are stored.
Create a record
for each table that needs a unique number key.
In the above table, there is a specific starting number
each type of information. Customers
are to be started from number 100
. Invoice numbers
start from number 25000.
In your VBA code, when you need a new ID number, call this function
with the name of the item to be numbered.
newOrderNumber = getID "ORDER"
Here is the code for the getID function
Public Function getID(fld As String) As Integer
Dim dflt As ADODB.Recordset
Dim rcmd As ADODB.Command
Dim strsql1 As String
Dim strsql2 As String
Create a recordset by selecting the tblDefaults codeNumber where CodeName equals the value sent as a parameter.
Set dflt = New Recordset
strsql1 = "SELECT CodeNumber FROM tblDefaults WHERE tblDefaults.CodeName = '" + fld + "'"
dflt.Open strsql1, gConn, adOpenDynamic, adLockPessimistic, adCmdText
' Place the CodeNumber in the getID variable.
getID = dflt!CodeNumber
' Create a command object to update the tblDefaults with the next CodeNumber.
Set rcmd = New Command
rcmd.ActiveConnection = gConn
rcmd.CommandType = adCmdText
strsql2 = "UPDATE tblDefaults SET [CodeNumber] = " + getID + 1 + _
" WHERE [CodeName] = '" + fld + "'"
rcmd.CommandText = strsql2
After calling the getID
will contain the value 1001
and the value in the default table will be 1002
Copyright © 2006-2018, LQSystems,Inc. All rights reserved.