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

    Unanswered: Sequential Numbering

    numbering system on an invoice. I have it on a save button with an onclick() event. Once customerID is chose, then the items for the invoice, I click the button addrec. Below is what I am using to generate the invoiceNo. It is suppose to look for the highest number, then add 1 to it.
    Private Sub addrec_Click()
    InvoiceNo = Nz(DMax("[InvoiceNo]", "[OrderNo]"), 0) + 1
    On Error GoTo Err_addrec_Click
    DoCmd.GoToRecord , , acNewRec

    Exit_addrec_Click:
    Exit Sub

    Err_addrec_Click:
    MsgBox Err.Description
    Resume Exit_addrec_Click

    End Sub
    I followed this rule posted by John? MVP. In the above, InvoiceNo, is the text box I want to fill in on the form. OrderNo is the table that stores the invoiceID and OrderNo. Though it only generates 1 and will not move onto number 2.

  2. #2
    Join Date
    Apr 2012
    Posts
    28
    The second argument of DMax should be the name of the table not a field, so if [OrderNo] is a field then you must change it to the name of the table (e.g. [Order Details])

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally you would be better off declaring this as a function that returns a value rather than a sub which sets a value. why.. its called modular code. you could move this function to a code module, paramerterise it and use it elsewhere. its such mickey mouse code that it probably isn't worth doing it for that reason, but it iw worth doing for another reason. it makes your code easier to read, easier to understand, and not just for the poor sap who has to follow you trying to fix a bug somewhere. bear in mijnd that poor sap may be someone else, it may be you 2..3 years down the line when you've forgotten what this does

    However looking at that code
    im suspicious of
    Code:
    InvoiceNo = Nz(DMax("[InvoiceNo]", "[OrderNo]"), 0) + 1
    if invoiceno is a control Iwoudl have expected the compiler to throw a wobbler and whine about cannot assign value to object or similar
    I would have expected
    InvoiceNo.value = Nz(DMax("[InvoiceNo]", "[OrderNo]"), 0) + 1
    check you don't have a variable called InvoiceNo.

    it may also depend on where you call addrec.

    have you tried puttign a breakpoint on the code and stepping through it to make certain the logic the form uses is the same as you actually expect.
    Error Handling and Debugging Tips for Access 2007, VB, and VBA
    or more generally
    Google
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2012
    Posts
    12
    Well Originally I had an tables:

    Invoice(InvoiceID, InvoiceNo, CustomerID, Order Date)
    InvoiceRows(InvoiceID, InventoryID, Quantity,UnitPrice, InvoiceRowsID, TotalPrice)
    OrderNo(InvoiceID, InvoiceNo)

    When I changed the second InvoiceNo to Invoice(table) it started to popular the numbers for me. But Nothing is showing up in the OrderNo table. I have a one to one relationship with Invoice, InvoiceID being the PK.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if InvoiceID is the PK, then why are you using InvoiceNo in the function to find the current maximum invoice no
    To resolve this I think you will be best to post your db here. sanitise it so there is nothing sensitive in the db. delete every form / report which isn't relevant to the problem
    compact and reapir the d
    then stuff it into a zip file and post here as an attachement
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2012
    Posts
    12
    I read that using the Autonumber as your invoice numbering system is not a good idea, that is why I made InvoiceNo. I guess you could run the same code for InvoiceID's autonumber as well?

    I moved the Dmax code to Form_onload() so that I could see the actual invoiceNo be generated. With it under onclick(), it will not show the invoiceno, only generate it for storage.


    EDIT-
    just updated zip file
    Attached Files Attached Files
    Last edited by potatoehead; 04-30-12 at 14:11.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you believe yo uneed an autogenerated number Invoice AND a sequential Invoice number.
    I guess it depends ont he model of business practice you are following but I dont' underrstand your current design
    there seems to be confusion between an invoice and an order.
    I would expect an order to have row details (details of what products were ordered + what proce and so on.

    an invoice may comprise one or several orders (not none), but an order is separate from an invoice. you coudl have sevearl orders without an invoice as an invoice isn't allocated untill generated.


    so which form has the problem you are struggling with
    what is the design / process you expect to follo to get the new sequence number
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2012
    Posts
    12
    I added the OrderNo table to make a sequencing numbering, some like this Custom autonumbers

    So an invoice may comprise one or several invoicerows (not none), but an invoicerow is separate from an invoice. You coudl have several invoicerows without an invoice as an invoice isnt allocated until generated.
    It seems like that is what the table relationship is showing, am I on track?

    The form is the Process Order. The code is now under the form_OnLoad(), which is not working for me because every time I close the form and open it, a new invoice number appears, which is what it should do, but not what I want.

    I would like if the invoiceID would be the date plus invoiceID.
    Ex: 12102012-01
    12102012-02
    04302012-02

    New Attached file. did a little changing
    Attached Files Attached Files
    Last edited by potatoehead; 04-30-12 at 18:04.

Posting Permissions

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