Sequential Number in Access form(invoice box) (1 Viewer)
- Thread starter kanxay
- Start date
I too use the DMax() function .My example is activated on the after update event of the Last Name , you can use what ever suits you.
#If IsNull(Me.OrderNumber) Then
Me.OrderNumber = DMax("OrderNumber ", "Invoices") + 1
End If#
I have not post for awhile so am just offering my example .
Regards Ypma
#If IsNull(Me.OrderNumber) Then
Me.OrderNumber = DMax("OrderNumber ", "Invoices") + 1
End If#
I have not post for awhile so am just offering my example .
Regards Ypma
- Local time
- Today, 14:50
- Joined
- Feb 19, 2013
- Messages
- 16,607
in a multi user environment better to use the form before update event or perhaps the before insert event rather than when fields start to be completed, otherwise two users may end up with the same value. At the very least, run a check in one of those events to make sure another user has not already taken that number - in which case renumber and advise the user the number has changed
- Local time
- Today, 14:50
- Joined
- Feb 19, 2013
- Messages
- 16,607
what is wrong with using invoiceID?
if you want a separate invoice number field, then create a 'invoicenumber' field and use the dmax function to find the latest number - then add 1
here is a link to the dmax function
https://support.office.com/en-ie/article/dmin-dmax-functions-f5c6de87-8752-4f29-ab4b-c554053d299f
if you want a separate invoice number field, then create a 'invoicenumber' field and use the dmax function to find the latest number - then add 1
here is a link to the dmax function
https://support.office.com/en-ie/article/dmin-dmax-functions-f5c6de87-8752-4f29-ab4b-c554053d299f
missinglinq
AWF VIP
- Local time
- Today, 09:50
- Joined
- Jun 20, 2003
- Messages
- 6,423
I see this advice, from time to time...and believe I even saw it in a hack by Allen Browne years ago...but in today's VBA it's inappropriate for this task.
The Form_BeforeUpdate event is, indeed, the event to use, as it fire at the last instant before the Record is saved, thus dramatically decreasing the chance of two Records getting the same number.
The BeforeInsert event, on the other hand, fires the instant a single character is entered in any Control...and makes it totally inappropriate for this kind of thing, in a multi-user environment! Testing shows it even fires prior to the OnDirty event!
Linq ;0)>
I already separate invoice table and field ! But I mean that in the invoice form ! The number do not sequence
- Local time
- Today, 14:50
- Joined
- Feb 19, 2013
- Messages
- 16,607
in the db you provided your invoice table has an ID field (autonumber), it does not have an invoice number field.
Autonumber fields are not editable - so you need another field for your invoice number
autonumbers do generally increment, but you will get gaps if you start to create an invoice and then don't save it.
If your accounting function cannot live with gaps in the number sequence then as advised, you need another field and use the dmax+1 method to populate it
I link between invoice table and invoice details table and get relationship between AutoinvoiceID to invoiceID field . And I count the Number Invoice form at ( invoiceID field invoice details table ) the question which table should I create more Invoice field ????
- Local time
- Today, 14:50
- Joined
- Feb 19, 2013
- Messages
- 16,607
you use the autonumber primary key to link to the details table as you do at the moment. The new invoice number field only needs to be in the invoice table and is not used to link to any other table
Well ! thanks you very much CJ_London .
I link all the table together so I cannot create Dmax function . If I want to do that I have to separate or create new table . So that all over I cannot do like that ! Thank again
I link all the table together so I cannot create Dmax function . If I want to do that I have to separate or create new table . So that all over I cannot do like that ! Thank again
- Local time
- Today, 08:50
- Joined
- Feb 28, 2001
- Messages
- 27,142
If you have something to prevent using a DMax+1 approach, you have a bad design that has already gotten in your way.
An autoincrementing number even autoincrements - but does not reclaim the number - in the case where you start but then cancel the operation that caused the number to be reserved. Autonumber fields CANNOT be used for something that has specific meaning like "the next available invoice number." There is one AND ONLY ONE possible use for any autonumbered field. You can use it as a confirmation ID after-the-fact because it is a guaranteed unique ID number. But you CANNOT guarantee that autonumber fields will be consecutively numbered without missing any numbers. That is why folks are telling you to use a "DMax+1" variant.
An autoincrementing number even autoincrements - but does not reclaim the number - in the case where you start but then cancel the operation that caused the number to be reserved. Autonumber fields CANNOT be used for something that has specific meaning like "the next available invoice number." There is one AND ONLY ONE possible use for any autonumbered field. You can use it as a confirmation ID after-the-fact because it is a guaranteed unique ID number. But you CANNOT guarantee that autonumber fields will be consecutively numbered without missing any numbers. That is why folks are telling you to use a "DMax+1" variant.
- Local time
- Today, 14:50
- Joined
- Jul 9, 2003
- Messages
- 16,271
You can lead a horse to water, but you cannot make him drink!
Sent from my Pixel 3a using Tapatalk
Sent from my Pixel 3a using Tapatalk
missinglinq
AWF VIP
- Local time
- Today, 09:50
- Joined
- Jun 20, 2003
- Messages
- 6,423
Can't make him do the backstroke, either...no matter how hard you try! :banghead:
Linq ;0)>
missinglinq
AWF VIP
- Local time
- Today, 09:50
- Joined
- Jun 20, 2003
- Messages
- 6,423
I don't know why this has persisted, even among knowledgeable people such as yourself, but it has. As I said, I'm pretty sure I've even seen it in some of Allen Browne's hacks. I really have to wonder if it performed differently in the early versions of Access. I've always ignored it when seeing it previously, but decided to test it this time. I was really surprised to see that it even fired before the OnDirty event.
Linq ;0)>
- Local time
- Today, 14:50
- Joined
- Sep 12, 2006
- Messages
- 15,638
In passing, for an invoice number, I would be tempted to store the NextNumber in a separate table, and increment it in the table after issuing a number. That way, you can re-seed the number to a different sequence if you require. Of course it then works like an autonumber, in that having issued a number, you "burn" the number if you decide you no longer want that invoice to be raised.
- Local time
- Today, 14:50
- Joined
- Sep 12, 2006
- Messages
- 15,638
@UncleGizmo
You can lead a horse to water, but a pencil must be lead.
You can lead a horse to water, but a pencil must be lead.
Business questions;
1) Are you required to account for each invoice number, even if it is not used?
2) What happens if you start a transaction but it is not finished?
3) What happens if someone decides not to make a purchase?
Depending on what you need to do with the invoice number, you may need to wait until you complete a sale before you generate the number. For linking detail records to the invoice itself, do not use the invoice number. This should be a display only value that is used to reference the sale by people, not the database.
Depending on your companies business rules you may need to assign the invoice at the beginning of a transaction but mark it as "void" or "Cancelled" if the sale is not finished. This is why knowing what business rules you use helps determine when to assign the next invoice number.
For the code, you would be using
Code:
Me.InvoiceNumber = nz(DMax("[InvoiceNumber]","Invoice Table"),0) + 1
