autonumber - if user doesn't fill info, missing numbers

[deekras]

i have my invoice number set to autonumber. if the user deletes or cancels the order while entering, that number is lost and theni have gaps in my invoice numbers. any sugestions?

 

[Atomic Shrimp]

I think this is one of those quirks with autonumbers that we are stuck with; it also happens if you cancel an append query at the 'are you sure?' dialog.

One possible workaround for you (although it would require a fair bit of change to your design) might be to have the user enter the details into unbound text boxes and only add them to the table when the user has clicked some sort of 'commit' button (you could then have all kinds of validation code in the OnClick event, but before the append action so that if the user tries to leave a field blank or enters a wrong value they are prompted before Access tries to write to the table).

HTH

Mike

 

[R. Hicks]

You should not use an autonumber to generate your invoice number for this very reason. An autonumber is not designed for any useful purpose except to create a unique indentifier for the record. You should consider using an integer datatype instead, then to increment the number use the DMax function + 1 to get the next invoice number.

Just a thought,
RDH

 

[deekras]

how would i write that? where would i put it?

 

[R. Hicks]

You will need to add a field to the table for the Invoice Number, set this field as a Long Integer datatype. You will have to manually set the first Invoice number to "1" because the the count will begin at "0".

Now to use this field in your form:
Add a txtbox on your form, set the default property for this txtbox to:

=DMax("YourInvoiceField", "YourTableName")+1
(change the names above to the actual names of your objects)

You need to disable and lock this txtbox or the user will be able to alter this Invoice number.
Now each new record will have an Invoice number that is the Max number used in the table (highest number) and add 1 to it, thus the next sequential number. Should the record be cancelled, the next record will again check for the Max number in the table and again add 1 to it. This will eliminate the gaps. Should you delete a record where the Invoice number is anything but the Max number, you will develope a gap.

You can add formating to the txtbox if you need a 5 digit appearance. Just add 00000 in the format property of the txtbox or any control where you want the invoice to appear as the 5 digit format.

Hope I have expained this well enough for you.

Good Luck,
RDH

[This message has been edited by R. Hicks (edited 02-13-2001).]

 

[Atomic Shrimp]

Just a concern here... in a multi user environment, might the Dmax method (which I like btw) result in duplicated invoice numbers?, if two users open their form at the same time (the new invoice number isn't added to the table until the update event is it? I'm thinking out loud here, so I might be wrong....

Mike

 

[R. Hicks]

Gee Mike.....is nothing ever simple? You may have a valid point here. Never really thought about this scenario.

This could then cause attempts at duplicate numbers and also other gaps in the number.

RDH

 

[Atomic Shrimp]

Sorry

(Don't shoot the messenger)

One possible way aound this would be tio give each user their own unique Alpha Invoice Prefix (the invoice ref field would have to be text though)
So if the prefixes were all 2 characters, and mine, for example was "MG", then to generate the next invoice number you would need:

=UserPrefix & (DMax("Val(Mid([YourInvoiceField],3))", "YourTableName", "[YourInvoiceField] like '" & [UserPrefix] & "*'")+1)

This assumes that you have previously stored my user prefix in a string variable called UserPrefix.

So it gets the highest numeric part of any invoice number with my prefix, adds 1 to it and prefixes the resulting number with MG again.

This would mean though that you would end up with invoice refs like:
MG101 and
RH101

where, although the prefix is different, the numeric part is the same; this may or may not be a problem in your setup.

I suppose you're now beginning to wonder if missing autonumbers are such a big problem after all...

HTH

Mike

[This message has been edited by Mike Gurman (edited 02-13-2001).]

 

[BarkerD]

You can avoid the issue of duplicated invoice numbers in multi-user environment by generating the new number on the Before_Update event. That way the number is incremented only moments before it is posted.

This would not work very well for a sub-form, though

Just a thought
Duane Barker

 

[PhilipEwen]

You've probably solved this by now, but surely the best way of stopping someone deleteing the autonumber is to lock the field, so nobody can change it.
I'm sure what was previously said about not using autonumbers as invoice numbers is also correct, but surely this is an easier way of dealing with your problem ??

 

[myhnews]

Hi everyone,

I always used a custom AutoNumber (DMax function), until recently I realized that a lot information is not correct. The reason is, because of the duplicated auto numbers.

The fist thing I did is a search here. This thread is exactly my situation. The duplications are happening when userA opens the form for a while; in the mean time user2 opens the form and saves it first.

I can not use Prefix as Mike suggested, since the auto number must be a number and its being referenced to several sub tables.

Would anyone know of any other solution?

Thank you
Joe

 

[Pat Hartman]

In probably 90% of the cases where people are generating their own sequence numbers, autonumbers would work just fine, gaps and all. In the small percentage of cases where it is actually necessary to generate your own sequence numbers - pre-printed forms such as checks and human readible ID's in replica sets (autonumbers in this case are random and can be negative so they don't make useful customer numbers for example) are two that come to mind - you MUST take into account the possibility of generating duplicate numbers.

I suggest generating the number at the last possible moment in the FORM's BeforeUpdate event to get past the problem of users starting a record but taking a while to finish it. This is still not fool proof so you still need to trap the duplicate record error and either tell the user to save again or you create a limited code loop (10 tries) to generate a new number. If you try 10 times, just return back to the user with a message to try again.

 

[myhnews]

I like your idea of trying 10 times.
However, would you have such a loop code that I can use?

Thank you
Joe

 

[myhnews]

Since the form has a sub form, and when entering the sub form the record get saved, I added the following codes.

Can you please check if this is the right way?

Thanks
Joe

Private Sub Form_AfterInsert()
If IsNull(Me.OrderNo) Then
Me. OrderNo = DMax("OrderNo ", "tblOrder") + 1
End If

Private Sub cmdSave_Click()
If IsNull(Me.OrderNo) Then
Me. OrderNo = DMax("OrderNo ", "tblOrder") + 1
End If