Question about Autonumber

[Reaper]

I have a db for work orders. On the form for creating a new work order I use an Autonumber field for the Work Order #. When the user starts entering data the autonumber increments to the next number. If the user decides to abandon the entry and exits, my vba deletes the current record. This causes the loss of consecutive work order numbers. Normally this isn't a problem, but auditors may question the missing "work orders" in the db.



Is there a better way to do this or a way to reset the autonumber to the lost value?

[RuralGuy]

AutoNumbers are not really for public consumption.
This should work for you: http://www.baldyweb.com/CustomAutonumber.htm

[June7]

If you run Compact & Repair on the db before entering another record, the table will pick up with the 'missing' number.

If you need an identifier to be sequential then need code that will manage the data entry to prevent gaps or account for 'aborted' or 'voided' records. It can be complicated, especially in a multi-user db.

I do this for an autonumber in one table and a custom unique identifier in another.

I know I shouldn't care about missing IDs in the autonumber sequence but when I first built the db I had code that depended on no gaps. I have since changed that code but retained the code that prevents gaps anyway.

Every custom unique identifier is accounted for. Since I generate the ID when record is initiated, if user then aborts, record is saved with fields blank except for the ID, then this record is pulled up and used for the 'next' entry. A completed record can later be 'voided' and this is also documented.

But then sequence gaps are the least of your concerns if you have auditors looking over your shoulder because any of the data can be modified in existing records. Access is not the most secure db application. I worked for a non-profit that used Great Plains, a proprietary accounting software. I don't know what it was programmed in but certainly not Access. It cost tens of thousands of dollars but you could be sure the data was secure. Records could not be altered.

[Reaper]

Ok, I think I get it. Don't use the system autonumber for the sequential work order number. Create a method of generating and controlling it myself. I understood what Baldy was saying in the link, but I don't know how to actually do it. Any help or point me in the right direction?

[June7]

Generating custom unique identifier is a common topic in forum. Here is one https://www.accessforums.net/forms/a...ing-23329.html

[Missinglinq]

...I get it.Don't use the system autonumber for the sequential work order number...

Right! Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each Record! They should not be used as account numbers, serial numbers, etc. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how Autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

You'll notice that he particularly addresses their not being for use by accountants and auditors!

Linq ;0)>

[Reaper]

Alright, so I added a field in the WorkOrders table for a sequence number. I threw in numbers in the 8 lines that already exist so I have some data to work with. Then, adding the code to find the largest sequence number and then +1 for the new one. Getting held up with an error on the DMAX coding. I'm getting error #2428 for an invalid arguement in the aggregate function. My code for that is as follows:

NewSequence = Nz(DMax("[lngSequence]", ”tblWorkOrder”), 0) + 1

I've check the syntax over and over and verified I have everything spelled right. What gives?

[Missinglinq]

You've got the parameters for the two Functions intermixed; the red is part of the DMax() Function, while the black is part of the Nz() Function. So

NewSequence = Nz(DMax("[lngSequence]", ”tblWorkOrder”), 0) + 1

needs to be

NewSequence = Nz(DMax("[lngSequence]", "tblWorkOrder") + 1, 0)

but if

DMax("[lngSequence]", "tblWorkOrder")

is Null, it means that there are currently no Records in tblWorkOrder, and I'm guessing that you'd want the first Record to have a NewSequence of 1, not 0. If so, you actually need

NewSequence = Nz(DMax("[lngSequence]", "tblWorkOrder") + 1, 1)

Linq ;0)>

[Reaper]

Getting the same error. I've listed my code below. I created a command button for the code and display in a msgbox. It crashes on the DMAX function I think. If I removed the NZ function I get the same error.


Private Sub Command84_Click()

On Error GoTo Err_Handler

newsequence = Nz(DMax("[lngSequence]", ”tblWorkOrder”) + 1, 1)
MsgBox newsequence
Exit Sub

Err_Handler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbCritical, "Sequence Error"
Exit Sub

End Sub

[RuralGuy]

I would hope that you are using the same QUOTE character throughout. ["] It looks like you have more than one type of double quote.

[Missinglinq]

Don't know where you're writing your code, in a word processor, maybe, but apparently not directly into the code module. If you change

”tblWorkOrder”

to

"tblWorkOrder"

it'll run as intended. I would never have thought that the style of the Quotation Marks would matter, but testing shows that it does, indeed!

Linq ;0)>

Late note: Sorry, didn't see you there, Allan! As I posted, that is the problem!

BTW, how much snow do you have outside your window?

[Reaper]

I figured it out. Oddly, it was a bad field reference in another routine. It was failing on compiling.

[RuralGuy]

BTW, how much snow do you have outside your window?

Hi Linq,
We've only got maybe 6 inches so far. Most of the storms are tracking North.

[Reaper]

I'm on a laptop, entering directly into the code module. I only have one type of quotes available, so I have no clue why they look different. Anyway, fixing the bad field reference in another routine cleared the error I was getting.