- Numbered Query with DCount
- Running Sum with DSum
- Difference Between with DMax
- Rolling Average with DAvg and DCount
- Begin Date and End Date from Effective Date
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.
This opens the Product table, find the largest ProductNum and add 1 to it. That's it.
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)
This calls the IncrementField user-defined function, which looks like this:
If DataErr = 3022 Then
Me!ProductID = DMax("ProductID", "Product") + 1
IncrementField = acDataErrContinue
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.