The Database Journal
03/10/2006
Adjusting Autonumber Increments

03/10/2006
Moving Access Data to Microsoft Excel

03/10/2006
Exporting Access Data with TransferSpreadsheet

03/10/2006
Export Access Data with VBA

03/13/2006
Data Layout in Microsoft Excel

03/17/2006
Excel Text to Number Conversion

03/21/2006
Transposing Excel Ranges

03/25/2006
Access VBA Timer Event

03/29/2006
What is MySQL?

04/02/2006
Microsoft Access Parameter Query Tutorial Video

04/06/2006
How does PHP Work?

04/10/2006
Microsoft Access VBA Tools - References

Click here to view all articles.

Database Design
Website Design
Search Engine Marketing
Home
Contact Us
About Us
Database Journal
Newsletters
Database Newsletter

Adjusting Autonumber Increments

If you’re designing a database and don’t have any kind of unique identifier for your records, Autonumbering is the way to go. To get very basic, it provides a simple incrementing field that you don’t have to touch. Each new record gets a new number, nothing is duplicated, and it gives you the perfect primary key for use with related records in other tables.

An autonumber isn’t the best primary key, however. The best key is something like an account number used in your business, a social security number, an ISBN number, these types of keys are perfect because they relate to the subject in some way – which an autonumber also does, but not as closely as something like an SSN (though certainly having an AutoNumber AND an SSN is fine, even if it's a bit redundant).

Some developers are down-right annoyed at the autonumber because it starts with one and increments by one. Is it too simple? Perhaps. Too annoying? Might be. Well here’s a little trick to quench your desire for something likely far too over-complicated to ever justify using in a production database (that’s why it’s so much fun)!

We’re going to augment the autonumber by 10. Sorta. Really, it's not an autonumber field, but it will act like one! We’ll start out with the table design, it’s very simple – just two fields for our example:

The first field is called “Auto” and is a Number of Double data type.

The second field is a text field called “Color”. We’re just going to fill in basic colors to see how the autonumber works.

Save the table as “tbl_Colors” and flip from design view to datasheet view of the table (we’re going to enter in one record right here). In the Auto field for the first record, enter in the number 10. For the Color field, enter in “Orange” (see Figure 1 below).

Figure 1

Now we’re ready to build the query from this table that our form will be based on. The query is super-simple, just add both fields and make sure that the Auto field is set to sort Ascending. We’ll save that query as “qsel_Colors” – it will look just like the table.

Next we’re going to build a very simple form. Make sure the form’s record source is “qsel_Colors”, and add the two fields from your field list. The form should look something like Figure 2 below.

Figure 2

Now that we have the form built, we only need to add one little formula to make the jumping increment work. Go back into design view of the form, and open up the properties for the “Auto” text box. Under “Default Value” place the following formula:

DLAST(“[Auto]”,”qsel_Colors”)+10

The DLast function works with values in a domain (query or table). Basically, this function tells Access to look at the last value entered in “qsel_Colors”, add 10 to it, and make that the default value for the new record.

If you change the starting number to 4, the next record would be 14, then 24, and so on. You don’t necessarily have to stop there, of course, you can combine many functions (like Left, Mid, or Right) to come up with an auto incrementing account number that has letters as well (as long as the data type for the Auto field isn’t Double, naturally).

So the question begs to be answered: Why? It’s difficult to come up with a good reason to use this method instead of a standard autonumber unless it somehow fits a situation exactly. You’ll want to add code behind the form to double check for errors when entering data, and you should consider not allowing the “autonumber” to be edited by anyone other than someone who is very familiar with Access and keeps track of the quality of all entered data. By just disabling the Autonumber field so it can’t be edited, you’ll probably save yourself a lot of work that would be caused by rogue data-entry specialists changing numbers for the fun of it.

Even if you can’t justify using this technique, it is pretty nifty, and the function is a useful one both in forms and reports, and especially within VBA Code. Mess around with it and see how it works for your situation, it just might be the perfect fit!


©2014 Blue Moose Technology, LLC

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Adjusting Autonumber Increments