Sequential Number in Access form(invoice box)

[kanxay]

how to create sequential numbering invoice in access form ???

 

[theDBguy]

Hi. The usual approach is to use the DMax() function.

 

[ypma]

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

 

[CJ_London]

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

 

[kanxay]

how to create sequential numbering invoice in access form ???
please help me . i can't do it

 

[CJ_London]

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

 

[missinglinq]

in a multi user environment better to use the form before update event or perhaps the before insert event...

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)>

 

[CJ_London]

I stand corrected - thanks for pointing it out

 

[kanxay]

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

I already separate invoice table and field ! But I mean that in the invoice form ! The number do not sequence

 

[CJ_London]

I already separate invoice table and field

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

 

[kanxay]

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 ????

 

[CJ_London]

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

 

[kanxay]

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

 

[The_Doc_Man]

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.

 

[Uncle Gizmo]

You can lead a horse to water, but you cannot make him drink!

Sent from my Pixel 3a using Tapatalk

 

[missinglinq]

You can lead a horse to water, but you cannot make him drink!

Can't make him do the backstroke, either...no matter how hard you try! :banghead:
Linq ;0)>

 

[missinglinq]

I stand corrected - thanks for pointing it out

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)>

 

[gemma-the-husky]

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.

 

[gemma-the-husky]

@UncleGizmo

You can lead a horse to water, but a pencil must be lead.

 

[Mark_]

how to create sequential numbering invoice in access form ???

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

Me.InvoiceNumber = nz(DMax("[InvoiceNumber]","Invoice Table"),0) + 1

Note, I'm in the habit of always wrapping anything that can return a null in nz().