Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Unanswered: Invoice and subforms

    Hi everyone,

    I have a customer and invoice database I am working on. I have tables:
    CUSTOMER(CustomerID{PK}, FirstName, LastName, Address, etc)
    INVENTORY(InventoryID{PK}, ItemCode, Itemdetails,ItemUnitCost, ItemSalePrice)
    INVOICE(INVOICEID{PK}, CustomerID, OrderDate, Comments)
    INVOICEROWS(InvoiceRowsID{PK}, InvoiceID, InventoryID, Quantity, UnitPrice, InvoiceRowtax, Total Price)

    I am currently working on the invoice section. I have a form that shows InvoiceID, CustomerID, Comments and within that, a subform with the INVOICEROWS as continuous forms. Link Master and Child with INVOICEID. The INVOICEROW shows option to choose InventoryID, input quantity, UnitPrice, TotalPrice, and then a =Sum[totalprice] at the bottom.

    I am having trouble with the INVOICEID on the mainform to generate the next autonumber, it always says (New). THOUGH when I enter something in the comment section it will show a number.

    What am i doing wrong?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by potatoehead View Post

    ...I am having trouble with the INVOICEID on the mainform to generate the next autonumber, it always says (New). THOUGH when I enter something in the comment section it will show a number...
    You're not doing anything wrong, that's simply how Access works! It will not generate/assign an Autonumber until a New Record is created, and the New Record is not created until a single character has been entered into a Control.

    In addition, you really shouldn't be obsessing over this. Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:
    Quote Originally Posted by John Vinson
    When using Autonumber, do be aware that there will be gaps in the numbering ‑ any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

    In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, ‑1083225152 make such people get very nervous.
    If you want to your InvoiceIDs to be consecutive you'll have to redefine the datatype of the field to Text or Number/Integer (despite the "numbers" it contains, it really should be Text, as it's not to be used for math) and develop a hack for generating an auto‑incrementing InvoiceIDs.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    Okay, So i did some research and I found I should be doing sequential numbering. Some like this, take from ScottGem's Space

    e’ll start with the simplest, scenario 1. Acme will use a
    straight sequential number for its Purchase Order numbers. Add a (Long?)
    Integer field to the table named PONum.
    Add a control to your Purchase Order form bound to that field. Then use
    the following expression to populate that control:

    Me.txtPONum
    = Nz(DMax(“[PONum]”,”tblPO”),0)+1

    The Nz function will return 0 if there are no records so
    this expression will return a 1 the first time and then increment the highest
    number each additional time. The key is where to put this line of code. As I
    said it needs to be generated immediately prior to saving the record so that
    another user does not get the same number. So generally I would put this behind
    a Save button or a button to specifically generate the number. I would then add
    a second line to commit the records. Either:

    Me.Dirty
    = False

    or

    DoCmd****nCommand
    acCmdSaveRecord
    Now according to this quote, do I put a InvoiceNo in the INVOICE table, where he says
    Add a (Long?)
    Integer field to the table named PONum
    Or do I make a separate table to hold these? So Lets say I have:
    INVOICE(INVOICEID{PK},InvoiceNo, CustomerID, OrderDate, Comments)
    Then it would be:

    me.txtINVOICE
    = Nz(DMax(“[INVOICE]”,”InvoiceNo”),0)+1

    Yes, No?
    Last edited by potatoehead; 04-26-12 at 19:26.

Posting Permissions

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