GetID Utility
by Linda Quinn
Close Window
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
=================================================================
This content was created by Linda Quinn of LQNet.
See http://www.lqnet.com for a great
collection of articles on this and other topics.
=================================================================
Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.
====================================================================
Want an expert to help with your project?
LQ Systems, Inc.
Business Solutions
====================================================================