Auto Number in a Field (1 Viewer)

[Sodslaw]

Guys, I have a table called Order one of the fields is called [InvoiceID].
I have a button in a form that auto-generates a sequential Invoice number, by looking up the highest value and +1. I did this via a macro...

Set Value
Item = [InvoiceID]
Exp. = DMax("[InvoiceID]","Order")+1

But I periodically see duplicate invoice number that I need to reassign a new invoice number for and I'm sure this is not the best way to carry out this operation.

my database is split and I assume that something is not refreshing or buttons are being pressed at the same time.

can someone give me some advice on how to best handle this issue?

 

[Minty]

Set a unique index on that field and it shouldn't ever duplicate.

You will need to trap the error and retry the code in the event of a duplicate being generated.

 

[MarkK]

An Order and an Invoice are distinctly different things and I would expect them to be stored in different tables. It seems like an error in your system design, therefore, that you would be generating an InvoiceID in the Order table using DMax() + 1. I would expect an InvoiceID to be generated when you add a row to the Invoice table, and I would expect the index on that InvoiceID field to disallow duplicates, as Minty describes.
hth
Mark

 

[plog]

... that auto-generates a sequential Invoice number

What's so special about your invoice numbers? Why not just use an autonumber ID for them?

 

[Frothingslosh]

If two people create invoices at roughly the same time (ESPECIALLY if the number is generated in a different step than the actual record save), you're going to get duplicate numbers from this method.

Unless your invoice numbers have a specific formula to use (I've seen them include dates and times, for example), or you need the ability to modify them to reflect back orders, partial shipments, etc, you would probably be best off just using a simple autonumber.

I'm with the others in saying that you really should just consider a unique index or an actual autonumber field. If necessary, we can certainly step you through converting your table and any child tables if you go the autonumber route.

 

[jdraw]

Sodslaw,

It might be helpful to you and readers if you could describe your "business" in clear, simple English. As Markk has highlighted -Invoice is not same thing as Order. Readers can't give you more focused responses until we understand your specific points/issues within your business context.
If there is/are some special characteristics of your business, then please explain.

Good luck with your project.

 

[The_Doc_Man]

Just to add a comment: The word "Order" that you used for a table name is also a reserved word for SQL. Using a reserved word for a table name (or field name or control name, for that matter) is a good way to confuse Access. Which will in turn leave YOU confused when something stops working like you expected because there is a reserved word buried in the mix. The solution is to change the name of the table. And in this case, the reserved word is Order, singular, so you COULD get away with something so simple as to call the table Orders (plural).

 

[Sodslaw]

thanks, I really appreciate all of your feedback.
Let me explain and describe the business model and also the Order table that may have to be renamed Orders

We supply spare parts for Products that are within 12 months warranty (90%) these are free and to create an invoice is not required. however other fields in the Order table is required to be filled out, for example, the serial number of the product, amongst others. However, sometimes customers that are outside of the 12 months warranty (10%) want to purchase a spare part that must be invoiced.

There is an order table there is a field OrderID (auto number) So every order created on the system uses this reference and there are no duplicates. If a quote is required / proforma all of the relevant information is in 2 tables, Order and a subform OrderItems
When the odd Order requires to be invoiced then there is a field as described above for creating an invoice InvoiceID and InvDate. When an invoice is required to be created then these 2 fields are populated and an invoice is raised. I see no need to create another table called Invoice as all of the relevant info is already in the Order table unless it's literally a table with InvoiceID (being an Autonumber) and InvDate and setting a relationship between the invoice table and order.

The Invoice number defaults to a Null Long Int. Indexed and duplicates OK. originally my intention was to set this field to not allow duplicates
But before I did this I was asking the forum if my methods of using DMax("[InvoiceID]","Order")+1 to generate the invoice number is the best method to use.

 

[Minty]

Good clear concise description, thank you.

I might go with the second table, just to alleviate issues with null values in an field that has duplicates set to No, as I don't think you can have both of those possibilities as constraints.

 

[Sodslaw]

Thanks Minty,
I believe I am able to set no duplicates to a field that has multiple Null values but the populated invoice numbers must be unique.

 

[jdraw]

Is this a separate system from the one that manages/supports original Orders/Sales?
My understanding:

Customer buys a Product via Order and is given Invoice to make Payment.
If Product fails within Warranty, spare Part is sent to Customer (no charge).
If Product fails outside Warranty, spare Part is sent to Customer with Invoice showing actual charge and original OrderID is referenced.

I would include Invoice table and show relevant info (Customer/Order/Charge..) for completeness. Free replacement could show a 0 charge with reference to Warranty period.

Each table would have its own unique ID (primary Key--autonumber) --standard Access table design.

Good luck.

 

[Frothingslosh]

You can set No Duplicates on a field that allows nulls. However, in my experience, sometimes it allows multiple nulls, sometimes it doesn't, and sometimes it stops checking the index altogether, allowing any values to be duplicated. (We're having a nightmare scenario at work right now because of that last one.)

I really would advise against that solution and instead go with the multiple tables version.

 

[missinglinq]

If two people create invoices at roughly the same time (ESPECIALLY if the number is generated in a different step than the actual record save)

This is usually the cause of this problem...and you don't say (I don't believe) where/when you're generating this number. The number needs to be generated as the last thing in the Form_BeforeUpdate event...the last event to fire before a Record is actually saved. Doing so in this event, for years, I've never run into a problem with duplicates.

Linq ;0)>

 

[lpapad]

My proposal, use one table with auto-number field and another field to hold the InvoiceID.

Then use a 3 phase algorithm:

phase 1 -> create the actual new record with empty InvoiceID field and (after the record is saved in the database) mark the specific auto-number field of the new created record

phase 2 -> count records with criteria: auto-number field < new record's auto-number field (this counts all invoice records before the new invoice position)

phase 3 -> set empty record's InvoiceID of newly created record = counter of phase2 + 1

I propose to calculate the InvoiceID after saving the actual record, because if you try to calculate it before saving the record, you fall into the problem of shared memory access and then you must use a semaphore technique to address the problem, but then you have to face another problem: the mutual exclusion problem - all these gradually gets pretty complex.

My proposal simplifies the problem by counting the previously created records, in a static situation where all records are already commit in the database and eliminates the "shared memory access" situation.

 

[lpapad]

A schematic depiction of Mutual Exclusion situation: