GetID Utility
Printer Friendly Version
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.
| Customer |
100 |
| Order |
1001 |
| Invoice |
25000 |
| Inventory |
10 |
In the above table, there is a
specific starting number for
each type of information.
Customers are to be started from number
100.
Invoice numbers will
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
rcmd.Execute
dflt.Close
End Function
After calling the
getID function,
newOrderNumber will contain the value
1001 and the value in the default table will be
1002