Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Invoice Numbering Incremental Loop

    I have a query named UninvoicedWorkOrders
    it shows a list of work orders that have not been invoiced



    WORKORDERNO WOTOTAL INVOICED
    1000 500.00 N
    1020 200.00 N
    1066 100.00 N
    1077 300.00 N


    I want to loop thru the query and append an invoice entry into tblInvoices - but need to increment the invoice number by 1

    I also have a table named tblInvoices

    INVNO INVDATE WORKORDERNO
    100 2/16/05 1000
    101 2/16/05 1020
    102 2/16/05 1066
    103 2/16/05 1077


    after that - I will update the matching records in the work order table INVOICED to Y

    How do I create a loop to loop through qryUninvoicedWorkOrders and Increment the Invoice Number as it adds the new records?


    Any help is appreciated..

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    One quick question - given that the invoice no looks like a simple integer field - why didn't you just use an autonumber and have Access do the work for you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Good Question - Here's Why

    Quote Originally Posted by pootle flump
    Hi

    One quick question - given that the invoice no looks like a simple integer field - why didn't you just use an autonumber and have Access do the work for you?

    I usually do it this way - however, the company I am developing this for wants to use a table to set the next invoice number - which may be 10000 this year,then next year start at 20000 year later 30000.

    Thre is a bit more to it as well, but the above explains it..

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok doke. Not 100% certain but I think you could still get JET (I was wrong to say Access earlier....) to do this by programmatically resetting the seed each year.

    I would contemplate a function and work this out as a set but there is a risk if you are going to be giving this to users... I'll pop down some code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Personally whenever I've hit this problem I've always fought hard to use an autonumber, often the user requirement is quite bizzaire, or the old stand by "we have always done it that way", or we wantr to mask the number of invoices we have sent (although this doens't look tenable in this case).

    However lets presume you can't use an autonumber then you need to store somewhere a row which has the last used number in it. I know we have answered this requiremnt several time sin the last month or so, so have a search of the site and see what you return.

    The general proces is something like
    -secure and exclusive lock on the row containing yoour next number.
    -retrieve the number
    -take a copy for your invoice
    -increment the number
    -write the next available number
    -release the lock
    -write your inovice

    The only wrinkle I can see here is if the invoice numbers must be sequential for the customer, is that instead of incremting it by one, increment it by the number of invoices you want to create
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •