Domain Function Example: Simulate AutoNumber with DMax

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

1. Numbered Query with DCount  
2. Running Sum with DSum  
3. Difference Between with DMax 
4. Rolling Average with DAvg and DCount
5. Begin Date and End Date from Effective Date 

One of the classic uses of a domain function is using the DMax() to generate your own "autonumber" field. The reason you would want to do this is if you want an unbroken sequential number field. The Autonumber data type cannot guarantee an unbroken sequence, so if you want one, you have to develop it yourself.

Single-User Application

While there are several ways to do this, one of the simplest is to use a DMax function in as the Default Value of a control on a form.

So let's say I want to generate my own sequential Product Number for a product table. I can create a form based on the Product table, and create a textbox bound to the ProductNum field. In the DefaultValue property, I would put the following domain function.

=DMax("ProductNum","Product")+1

 This opens the Product table, find the largest ProductNum and add 1 to it. That's it.

Multi-user Application

It becomes a little more complex in a multi-user environment, since two or more people may try to select the same number at the same time. There's a fairly simple solution for multi-user collisions, but it requires a tiny bit of VBA code.

First of all, the field you're incrementing must be a Primary Key or have a Unique Index on it. When you try to save the record, if the number has already been used, it will throw an error (3022). You can trap this error in the OnError event of the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Response = IncrementField(DataErr)

End Sub

This calls the IncrementField user-defined function, which looks like this:

Function IncrementField(DataErr)

   If DataErr = 3022 Then

     Me!ProductID = DMax("ProductID", "Product") + 1

     IncrementField = acDataErrContinue

   End If

End Function

So, if you have a collision, the IncrementField function will go out and grab another.

You can find a working example of both the single-user method and the multi-user method on my website here: AutonumberProblem.mdb.

.