January 19, 2007 at 2:17 am
I have an adp connected to sql 2000. This is an accounting software where invoice numbers are incremented when the users input the invoice date. This is working fine but in some occasions when two users are working simultaneously, they may get the same invoice number.
The code I use to update the invoice no is for the after update event of print date:
Dim maxAirlineInvoiceNo As Long
maxAirlineInvoiceNo = DMax("[InvoiceNo]", "[tblInvoice]")
Me.InvoiceNo.Value = maxAirlineInvoiceNo + 1
If Me.Dirty Then
Me.Dirty = False
End If
Me.cmdSave.SetFocus
Me.PrintDate.Enabled = False
Exit Sub
Me.Requery
Although this does not happen always, it is crucial to keep unique invoice numbers becasuse this is an accounting software
January 19, 2007 at 7:10 am
Make a primary key with an autonumber column.
Then add a calculated field to show the formated invoice number :
Formula = RIGHT("000000" + CSTR(PK), 7)
This allows for 9 999 999 invoices in the system. Should be plenty for you :-).
January 19, 2007 at 10:08 am
this did not work. remember it is an access project and I already have a table primary key (TxnID, int). I am following the golden rule of never using the primary key as part of the user form
January 19, 2007 at 10:17 am
Could you place an insert trigger on table to generate the next id as it's inserted?
In the user interface you can then display something like "New invoice" instead of an invoice number until the record has been saved and an invoice number generated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply