Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Microsoft Access: Invoice line number help (kind of an autonumber situation

    Hi, for invoices that have multiple items (well every invoice uses it) I have another entity that has the invoice number, line number, and product number for that line.

    So basically, I have something like this for one invoice that contains one item:

    Code:
    Invoice_Number     Line_Number     Product_Number
    1001               1               A1
    But when there are multiple items I have this kind of situation:

    Code:
    Invoice_Number     Line_Number     Product_Number
    1001               1               A1
    1001               2               A5
    1001               3               B7
    1002               1               A8
    1003               1               C9
    1003               2               A1
    My question is, how do I setup the Line_Number to count automatically + 1 per invoice_number. Right now Im having to manually enter Line_Numbers, which is pretty darn conducive to human error. Id also like to add something that doesnt allow duplicate line numbers per invoice if possible. I know that if I have a automatic count that shouldnt happen, but you know, accidents happen with human hands.

    Thank you for any help, the 3 books I have do not address this and it's hard to find this kind of thing via Google.

    P.S. I havent done a search on these forums yet, Im going to do that now while anxiously hoping for replies.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Theres no easy way of doing it, you are going to have to write some vba to sit behind the form.

    An approach would be to query the table each time a new record is added and retrieve the max(itemno) where inoviceno=currentinvoice. This could be written as a function and called on the form (or controls) "beforeinsert" event.
    the precise phrasing you woudl use depends on whether you are using ADO or DAO to read the recordsets.

    The sql would be something like
    "SELECT Max(MyItemNo) AS MaItemNo from tblInvoiceItems where InvoiceNo=CurrentInvoiceNo;"
    You would need to add one to that and then move that value to the bound control in the item field.

    You could modify the procedure to cater for cancelled items (eg if there was a cancelled item) by iterateing through a recordset looking for a gap (select ItemNo from tblInvoiceItems where InvoiceNo=CurrentInvoiceNo order by ItemNo;"

    to protect agianst duplicate item numbers make the primary key in the item table a composite key (InvoiceNo+ItemNo). Put some error handling code in the forms before/afterupdate events and trap from a duplicate record error message - no doubt someone can tell us what that error code is. But theoretically at least if you do not allow the userr to put in the item number then providing the sql is correct then you shouldn't need to trap the error. Also I would suggets it is unlikely that you will get a write conflict (in practise it would be highly unlikely that 2 people would wat to update the same invoice at the saem time. But its always prudent to trap soemthing that could make you application headbut.
    HTH

Posting Permissions

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