Richard
asked on
access: problem using auto numbers for invoices
I am using a simple database to generate Autonumbers to serve as Invoice numbers.
This works fine but strangely there are sometimes gaps in the Autonumbers.
We have five people using the db (but not heavily as we might generate five invoices a day, ten if we are lucky.
There is a front end on each desktop and a central back end for the data.
Number skips seem to occur about one in 15 invoices. I have back filled the AutoNumbers using an append Query, as we cant have missing numbers for accounts purposes.
I would guess that someone is abandoning an invoice half way thru and some how the AutoNumber is not saved.
However I cannot replicate the problem myself and I cannot recall losing Autonumber myself.
Anyone got any clue for how this is happening and how I might avoid it happening?
This works fine but strangely there are sometimes gaps in the Autonumbers.
We have five people using the db (but not heavily as we might generate five invoices a day, ten if we are lucky.
There is a front end on each desktop and a central back end for the data.
Number skips seem to occur about one in 15 invoices. I have back filled the AutoNumbers using an append Query, as we cant have missing numbers for accounts purposes.
I would guess that someone is abandoning an invoice half way thru and some how the AutoNumber is not saved.
However I cannot replicate the problem myself and I cannot recall losing Autonumber myself.
Anyone got any clue for how this is happening and how I might avoid it happening?
Barry Cunney
Does the application allow deleting of any records?
>This works fine but strangely there are sometimes gaps in the Autonumbers.
When a user starts to insert a row the AutoNumber will create. If the row is escaped before the insert commits, or if it's eventually deleted, then you would have a gap.
AutoNumber does not do a 'fill in the blanks'.
>and how I might avoid it happening?
The only chance you have of pulling this off would be to create another column (loosely called a 'Business Key') that is your InvoiceNumber, then write VBA code to insure that it populates as a sequential 1, 2, 3, ....
Then change your forms so that Invoice Number goes to that column, and not your AutoNumber.
The current AutoNumber column can stay the same, and participate in relationships.
When a user starts to insert a row the AutoNumber will create. If the row is escaped before the insert commits, or if it's eventually deleted, then you would have a gap.
AutoNumber does not do a 'fill in the blanks'.
>and how I might avoid it happening?
The only chance you have of pulling this off would be to create another column (loosely called a 'Business Key') that is your InvoiceNumber, then write VBA code to insure that it populates as a sequential 1, 2, 3, ....
Then change your forms so that Invoice Number goes to that column, and not your AutoNumber.
The current AutoNumber column can stay the same, and participate in relationships.
Autonumbers are generated as soon as a new record is begun. If the record is never saved it makes no difference, the number is used.
So autonumber fields always have gaps in the sequence.
If you require a continuous sequence then that has to be built in code.
So autonumber fields always have gaps in the sequence.
If you require a continuous sequence then that has to be built in code.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks,
could you use a code like this to start off at a particular number say, 1279, if I decided to abandon AutoNumber as a unique field and how would that look in the code.
could you use a code like this to start off at a particular number say, 1279, if I decided to abandon AutoNumber as a unique field and how would that look in the code.
"AutoNumber does not do a 'fill in the blanks'. "
But you can fill in blanks with an Update or Append Query.
"Autonumbers are generated as soon as a new record is begun. If the record is never saved it makes no difference, the number is used."
Although this behavior has varied over the years with different releases.
Just an FYI ...
But you can fill in blanks with an Update or Append Query.
"Autonumbers are generated as soon as a new record is begun. If the record is never saved it makes no difference, the number is used."
Although this behavior has varied over the years with different releases.
Just an FYI ...
Yes, you could "use code like this to start off at a particular number". Instead of :
If Me.NewRecord AND Cancel = False Then
Me.InvoiceNum = Nz(DMax("InvoiceNum", "tbl_Invoices"), 0) + 1
End If
you could use:
If Me.NewRecord AND Cancel = False Then
Me.InvoiceNum = Nz(DMax("InvoiceNum", "tbl_Invoices"), 0) + 1
if me.InvoiceNum < 1279 then me.InvoiceNum = 1279
End If
which would only be useful the for the first use, after that, it would return the larges value of InvoiceNum, which would always be greater than 1279.
If Me.NewRecord AND Cancel = False Then
Me.InvoiceNum = Nz(DMax("InvoiceNum", "tbl_Invoices"), 0) + 1
End If
you could use:
If Me.NewRecord AND Cancel = False Then
Me.InvoiceNum = Nz(DMax("InvoiceNum", "tbl_Invoices"), 0) + 1
if me.InvoiceNum < 1279 then me.InvoiceNum = 1279
End If
which would only be useful the for the first use, after that, it would return the larges value of InvoiceNum, which would always be greater than 1279.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks Guys,
Will do some tests and report.
Might be several days and perhaps some lost hair.
Will do some tests and report.
Might be several days and perhaps some lost hair.
MX - Yes I know, but the average developer isn't going to want to be on the hook for that stream of support.
topUKlawyer - fyed has you on the right track as far as the VBA to pull this off. I'll withdraw from the question now.
topUKlawyer - fyed has you on the right track as far as the VBA to pull this off. I'll withdraw from the question now.
"but the average developer isn't going to want to be on the hook for that stream of support. "
Not sure what you mean, but i was just sayin' ....
Not sure what you mean, but i was just sayin' ....
ASKER
Ok, in a five person light use multi user environment....
Is Fyed on the right track or is JDettman speaking sense?
Is it possible that D MAX hands out duplicate values and if so...
Is AutoNumber or Dmax the least likely to cause skipped/duplicates?
we will go with least likely trouble causing candidate
Is Fyed on the right track or is JDettman speaking sense?
Is it possible that D MAX hands out duplicate values and if so...
Is AutoNumber or Dmax the least likely to cause skipped/duplicates?
we will go with least likely trouble causing candidate
ASKER
Could you get round the duplicates by using "required, indexed, no duplicates" ????
Jim's method has the advantage that you don't have to find the maximum value, so in a production environment with a huge number of transactions, it will be both faster, and avoid the potential for duplicates.
But in your environment, 5-10 invoices per day, either method will work.
Yes, you could get around the duplicates by indexing the InvoiceNum field and setting it to NoDups, but would then have to add error handling code to recompute the number if it did generate a duplicate.
But in your environment, 5-10 invoices per day, either method will work.
Yes, you could get around the duplicates by indexing the InvoiceNum field and setting it to NoDups, but would then have to add error handling code to recompute the number if it did generate a duplicate.
ASKER
would that code be as long as Jim's! or a few lines
You should also look at why your accounting Requires sequential numbers with none missing. Certainly most businesses can't operate that way, they would come to a grinding halt at times if they did.
> .. look at why your accounting Requires sequential numbers with none missing
This is standard procedure in at least most European countries. Unused numbers has to be accounted for as they otherwise could have been used for false invoices.
/gustav
This is standard procedure in at least most European countries. Unused numbers has to be accounted for as they otherwise could have been used for false invoices.
/gustav
ASKER
agreed, cactus_data
> it would be silly to define both an Invoice number and a Autonumber for the PK.
I think not because that what we do.
The reason is simple; you have to be able to create any number of invoices (drafts) but they each need a final approval. Thus the AutoNumber is used for any new invoice - even those that may be deleted later or just left unapproved - and the InvoiceNumber is first assigned when - as the last step of approval - the invoice is saved marked approved and locked for further editing.
The InvoiceNumber is found via a simple lookup in a system table where the latest invoice number is stored; this gets updated by one, and that number is applied the current invoice.
This has worked for a decade but volume is low. For a high-volume system you would need a more sophisticated method.
/gustav
I think not because that what we do.
The reason is simple; you have to be able to create any number of invoices (drafts) but they each need a final approval. Thus the AutoNumber is used for any new invoice - even those that may be deleted later or just left unapproved - and the InvoiceNumber is first assigned when - as the last step of approval - the invoice is saved marked approved and locked for further editing.
The InvoiceNumber is found via a simple lookup in a system table where the latest invoice number is stored; this gets updated by one, and that number is applied the current invoice.
This has worked for a decade but volume is low. For a high-volume system you would need a more sophisticated method.
/gustav
<<The reason is simple; you have to be able to create any number of invoices (drafts) but they each need a final approval. >>
That's a different situation. I don't know of many systems that allow multiple drafts of an invoice. In fact I think most auditors here in the US would cringe at a system like that.
Most systems structure this as allowing for multiple proposals (each with a unique number), that turn into orders (or possibly invoices) when approved and are distinctly seperate things.
But to do this:
ID - Autonumber - PK
InvoiceNumber - Number - CK1
in a single table is just wasteful.
Jim.
That's a different situation. I don't know of many systems that allow multiple drafts of an invoice. In fact I think most auditors here in the US would cringe at a system like that.
Most systems structure this as allowing for multiple proposals (each with a unique number), that turn into orders (or possibly invoices) when approved and are distinctly seperate things.
But to do this:
ID - Autonumber - PK
InvoiceNumber - Number - CK1
in a single table is just wasteful.
Jim.
It is not multiple drafts of an invoice but drafts of multiple invoices.
/gustav
/gustav
> .. look at why your accounting Requires sequential numbers with none missing
This is standard procedure in at least most European countries. Unused numbers has to be accounted for as they otherwise could have been used for false invoices.
That is a leftover from the days when invoices were paper forms that were numbered sequentially and were the only way to send out invoices. While there is nothing wrong with that idea, it is probably not sufficient to prevent fraud or fake invoices in the electronic age.
In general, an autonumber column is intended to be used to identify the row and nothing more. Trying to use it as an invoice number for example brings up the kinds of problems you're seeing.
This is standard procedure in at least most European countries. Unused numbers has to be accounted for as they otherwise could have been used for false invoices.
That is a leftover from the days when invoices were paper forms that were numbered sequentially and were the only way to send out invoices. While there is nothing wrong with that idea, it is probably not sufficient to prevent fraud or fake invoices in the electronic age.
In general, an autonumber column is intended to be used to identify the row and nothing more. Trying to use it as an invoice number for example brings up the kinds of problems you're seeing.