Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Getting 'Next UNUSED Form Number' in sequence.

Discussion in 'Information Technology' started by ChrisM, Jul 28, 2006.

  1. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.
     
  2. Loading...


  3. Gijs van Swaaij

    Gijs van Swaaij
    Expand Collapse
    Guest

    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:

    > 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.
     
  4. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    ChrisM wrote:
    > 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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:t66dnebtlYzmnBXZRVnyvA@bt.com...
    > 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.
    >
     
  6. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    ChrisM wrote:
    > 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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  7. ChrisM

    ChrisM
    Expand Collapse
    Guest

    "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
    news:94Vhg.47276$Lm5.30409@newssvr12.news.prodigy.com...
    > ChrisM wrote:
    >> 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.


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

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


    OK, Agreed, see later...

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


    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.
     
  8. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    ChrisM wrote:
    > 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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  9. ChrisM

    ChrisM
    Expand Collapse
    Guest

    "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
    news:Oo%hg.47656$Lm5.4479@newssvr12.news.prodigy.com...
    > ChrisM wrote:
    >> 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.
    >

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

Share This Page