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
After calling the getID function, newOrderNumber will contain the value 1001 and the value in the default table will be 1002

Copyright © 2006-2016, LQSystems,Inc. All rights reserved.

   Printer Friendly Page