Getting 'Next UNUSED Form Number' in sequence.

[ChrisM]

Hi,
I'm writing a multi-user system that produces invoices with distinct
numbers.
I need a query that will produce these distinct numbers. My initial thought
was to use autonumbers or similar, but consider the folowing scenario:
User 1 Starts to create an invoice. Allocated Invoice number 0001.
User 2 Starts to create an invoice. Allocated Invoice number 0002.
User 1 Decides that they no longer want to create the invoice at this time,
so quit from the screen.

It is not acceptable for the system to have 'holes' in the invoice numbers
allocated, so the next time an invoice is required, it should be as 0001.
Then the one following that will be 0003 (as 0002 has already been created
by User 2 above).

Anyone got a suggestion as to a simple way of allocating numbers that work
like this? The ideal would be autonumbers that go back and fill in any empty
spaces, but I know they don't work like that...

Cheers,

Chris.

 

[Gijs van Swaaij]

Hi,

First of all, I think you should consider making the "invoice number"
field a text field instead of a numeric field, otherwise you will lose
the trailing zeroes.

Second, if you want an invoice number like this, you will probably also
want an autonumber primary key that you can use to make relations with
other tables. You don't want such keys to be re-used. Trust me, I have
never regretted making an autonumber primary key.

You should probably make invoice number a text field and let a form
fill it in automatically when a new invoice is recorded. For instance,
you can make a (possibly hidden) textfield on your form with invoice
number as its record source and a query like this (assuming that your
table is called tblInvoices) as its default value:
SELECT Min([Invoice Number]) FROM tblInvoices

Alternatively, you can let the system only add the invoice number once
the user has confirmed his or her choice.

In any case, autonumbers will not go back to fill in empty numbers.


ChrisM schreef:

 

[Rick Brandt]

Hi,
I'm writing a multi-user system that produces invoices with distinct
numbers.
I need a query that will produce these distinct numbers. My initial
thought was to use autonumbers or similar, but consider the folowing
scenario: User 1 Starts to create an invoice. Allocated Invoice number 0001.
User 2 Starts to create an invoice. Allocated Invoice number 0002.
User 1 Decides that they no longer want to create the invoice at this
time, so quit from the screen.

It is not acceptable for the system to have 'holes' in the invoice
numbers allocated, so the next time an invoice is required, it should
be as 0001. Then the one following that will be 0003 (as 0002 has
already been created by User 2 above).

Anyone got a suggestion as to a simple way of allocating numbers that
work like this? The ideal would be autonumbers that go back and fill
in any empty spaces, but I know they don't work like that...

Cheers,

Chris.

The two most common methods...

Calculate the highest existing number in the table using DMax(), add one to that
and assign that new value to your record. The best time to do this is
immediately prior to saving the record for the first time so the BeforeUpdate
event of the form is the best event to use. Since BeforeUpdate fires every time
the record is saved you need a test so the number assignment only happens on new
records.

If Me.NewRecord Then
Me.ID = Nz(DMax("ID", "TableName"), 0) + 1
End If

The above works for moderately high levels of concurrent insertions by multiple
users. For very high concurrency another method is to use a separate table that
stores the next value to use. When assigning a number you use a code routine
that opens that table and imposes a lock on it so no one else can use it until
the process is complete. While the lock is in place you assign the value in the
table to your record and then increase the NextValue table value by one. Then
the lock is released.

I prefer the former because the second requires that ALL insertions properly
handle the incrementing of the secondary NextValue table or the system breaks
down. The DMax method is examining actual values in the primary table and does
not care how those records got there.

Neither method reuses all values should records be deleted, but the DMax method
will reuse the last number used if that record is deleted before additional
values have been assigned. Both differ from AutoNumbers in that the record
actually can wait until saving to "consume" a value whereas AutoNumbers are used
by the mere act of starting a record (at least with Jet tables).

You could of course combine these methods and reuse all deleted values. This
would be done by placing the value from a deleted record into a separate table
so it can be reused. Your number assignment routine would then check the
"recycle bin" table of numbers and only use the DMax() method when there are no
old numbers to use.

Personally if I want to avoid gaps I simply don't allow deletions at all and
just provide a field that can mark a record as "Voided" or similar.

 

[ChrisM]

Gijs and Rick,

Thanks for your responses. Unfortunatly solutions do not work as they do not
fill in any holes created by someone deciding not to create an invoice after
being allocated an invoice number. Also, the invoice number has to be
allocated straight away, or there is a possibility of two people being given
the same number...

I was thinking of somthing along the lines of having a table that always
contained all possibile invoice numbers (0000 - 9999) and a query which
effectivley says 'select lowest number from this table that isn't already in
the invoice table'.
If a user starts to create an invoice, immediatly write a new record into
the invoice table containing the next available number thus issued. If the
user then quits without wishing to keep the invoice, delete this row back
out of the invoice table.
Any reasons why somthing like that wouldn't work?

I should have mentioned, my application is written in C#, I'm just using
Access (Jet) as my back-end database.

Regards, and thanks for your input so far.

Chris.

 

[Rick Brandt]

Gijs and Rick,

Thanks for your responses. Unfortunatly solutions do not work as they
do not fill in any holes created by someone deciding not to create an
invoice after being allocated an invoice number.

Actually if you read my response again I do cover that.

Also, the invoice
number has to be allocated straight away, or there is a possibility
of two people being given the same number...

Neither of the two methods I gave would allow the same number to be derived
by different users except VERY rarely in the case of the DMax() method and
absolutely never in the case of the separate table method. An Error handler
that tries another number when ther is a collision easily hanlded that rare
case.

It is actually assigning the number too soon that leads to multiple users
getting the same value. The BeforeUpdate event would calculate and assign
the number to your record a fraction of a second before it is committed to
disk. The risk of a duplictae is very low using that event. If you assign
the number when the user starts typing, but has not yet saved (and might
decide not to) then THAT is when duplicates occur.

I was thinking of somthing along the lines of having a table that
always contained all possibile invoice numbers (0000 - 9999) and a
query which effectivley says 'select lowest number from this table
that isn't already in the invoice table'.

That is how my "Recycle Bin" table works. The lowest old number is used
unless there aren't any in which case the DMax() + 1 value is used.

If a user starts to create an invoice, immediatly write a new record
into the invoice table containing the next available number thus
issued. If the user then quits without wishing to keep the invoice,
delete this row back out of the invoice table.
Any reasons why somthing like that wouldn't work?

I just don't see what a table containing all possible values gains you?

I should have mentioned, my application is written in C#, I'm just
using Access (Jet) as my back-end database.

Well yeah you should have certainly mentioned that. My suggested methods
are still valid you just have to use your own events instead of those
exposed in an Access form.

 

[ChrisM]

Actually if you read my response again I do cover that.

At first, I didn't agree, but after thinking it all through again, please
see my response at the end of this message.

Neither of the two methods I gave would allow the same number to be
derived by different users except VERY rarely in the case of the DMax()
method and absolutely never in the case of the separate table method. An
Error handler that tries another number when ther is a collision easily
hanlded that rare case.

It is actually assigning the number too soon that leads to multiple users
getting the same value. The BeforeUpdate event would calculate and assign
the number to your record a fraction of a second before it is committed to
disk. The risk of a duplictae is very low using that event. If you
assign the number when the user starts typing, but has not yet saved (and
might decide not to) then THAT is when duplicates occur.

OK, Agreed, see later...

That is how my "Recycle Bin" table works. The lowest old number is used
unless there aren't any in which case the DMax() + 1 value is used.


I just don't see what a table containing all possible values gains you?

It would mean I can write a simple query that will find the first 'hole' in
my list of invoice numbers (should one exist).

However, thinking about it. I can see how your policy of not allocating an
invoice number until a confirmed invoice is being actually written to the
database for the first time would solve a lot of, if not all the problems.
Only trouble is, that will require a certain amount of 'user re-education'
as in their existing system, they are used to seeing the new Invoice number
pop up as soon as they go into the invoice create screen. Not knowing the
invoice number until after they have hit 'Save' for the first time will
cause a certain amount of confusion at first, but I think they will be able
to get used to it...
In case you're wondering, none of the above is a problem in the old system,
as it is single user (in as much as only one user was ever able to create
invoices at any one time).

Thanks for your help on this Rick, I think I can see my way forward now.

Cheers,

Chris.

 

[Rick Brandt]

It would mean I can write a simple query that will find the first
'hole' in my list of invoice numbers (should one exist).

To that end someone (Mr. Steele perhaps) in here posted a really slick
solution to that. You create a query that is all of your existing used
numbers, another query that is a list of all of your used numbers (plus 1)
added to them. Then you just do a query for all numbers in the second query
with no match to the first and you get a nice list of all of the missing
numbers at the beginning of each gap.

The original solution actually did all of this with a single query using
subqueries, but using separate queries would be less complicated to put
together and would allow you to use the GUI query builder.

 

[ChrisM]

solution to that. You create a query that is all of your existing used
numbers, another query that is a list of all of your used numbers (plus 1)
added to them. Then you just do a query for all numbers in the second
query with no match to the first and you get a nice list of all of the
missing numbers at the beginning of each gap.

Very clever. I like that. Far far more elegant solution than mine, though
mine does give ALL missing numbers, not just those at the start of each
'hole'