Results 1 to 15 of 15
  1. #1
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69

    Create unique invoice numbers to update a table with records that may or may not have invoices.

    I am a new forum member so I apologize in advance.

    I run a landscape business that has a mowing service. I have a list of customers that I pull into a table on the first of every month. The plan is to create invoice numbers for these customers. I will then use those invoice numbers for the month. At the end of the day we check off the customers that we visited, query those customers and add that info to the invoice detail table. At the end of the month the invoices are sent out and the process starts over.



    Currently, I can pull the table, but I have to key the invoice numbers manually. I cannot create a loop that looks for 0 in the invoice column, generates a new invoice number and appends the number into the table continuing this until there are no 0's left in the column. I cannot seam to call a module to run. However, once the invoice number is added manually the rest of the process works quite nicely.

    I have not had a lot of experience writing a module or calling modules. Since I have not done many modules I am not sure if using them is the best avenue. My accounts are starting to grow so the manual addition of the invoice number is becoming a pain. The code I have is so mottled that I chucked it to start from the beginning after consulting with the forum! So anyone out there have any ideas? Thanks!

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Till someone comes along,
    1) Is the Invoice Number a Text field or a Number field ?
    2) Why do you add it manually & not have a autonumber field for Invoice Number ?
    3) Do you add the Invoice numbers in a numerical sequence after the last Invoice Number ( in continuation of the last Invoice Number ) in the table ?

    Thanks

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Why do you add it manually & not have a autonumber field for Invoice Number ?
    autonumber is not guaranteed to generate without gaps - and if there is one thing the revenue don't like to see - it's gaps in the invoice numbers range.

  4. #4
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    1. The invoice number is a number field
    2. Yaa, I could dump the list into the summary Invoice table then append to user table after auto number does its thing. That would work right?
    - I currently create new invoice numbers manually by query of largest number in the Invoice Summary Table then adding 1 to it then appending that same table.
    I am not using the auto number feature because I found I had issues using the auto number when calculating in queries. I have been using access for awhile now so perhaps this has been fixed.
    3. Yes invoice numbers are in numerical sequential order.

  5. #5
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    I started using Access with the 97 version. Back then you could not use the auto number field in a calculated query. Like using count and range etc. I assume that you can do that now. I just started using the 2013 Ver. a few months ago. Had been using 2007 but never challenged the auto number issue because the way I was using it was working.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can use a variation of the dmax()+1 routine.

    Dmax()+1 is intended to be used when inserting a single record, but you have a whole bunch of invoices to add in one go, so you need a variation.

    For that variation to work, you need a column or columns in your data which can be used to 'sort uniquely'. If you like, a temporary composite uniqueID for each row. For example if you (always) only do one invoice a day, the invoice date could be used, but if you do multiple invoices a day, it might be a combination of invoice date and customerid - but then if you might invoice the same customer twice in one day, you'll need to add another column, etc

    Find the value of the last posted invoice, then add a row number for the row in the table+1

    so

    NewInvNum: DMax("invnum","invTable")+DCount("*","somequery"," CKey<" & CKey)+1

  7. #7
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Just checked out autonumber. The tables are about 3 yrs old. Apparently I would have to start with a new table. Is there a way to get around this?

  8. #8
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Thanks! I will try this tonight. I am still struggling to understand the flow. If the auto number can adjust to account for the invoices that are already in the table then I would say that is the way to go. If not now that I can identify a new invoice number how to I get the program to identify those records that need invoices and then create that new invoice and then update that invoice field? Am I thinking about this correctly.... I hope there is an easy way to use the autonumber feature! Thanks!!

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Apparently I would have to start with a new table.
    Who told you that? I see no reason for needing a new table.

    If the auto number can adjust to account for the invoices that are already in the table
    Nope, autonumbers are not reliable for anything other than to identify a record. Since this is for invoicing, check with your accountant that he/she would be happy for there to be missing invoice numbers and credit notes in the same number range as the invoices.

    Sounds like you have another thread going on another forum, if so please provide the link, I don't want to waste my time arguing different points of view or thinking of solutions which others have come up with
    Last edited by June7; 04-15-2015 at 12:27 PM.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If autonumber will not serve because of possible gaps and can't depend on to be sequential (although I have never experienced the latter), will need code to generate identifier. Generating custom unique identifier is a common topic. Here is one for start https://www.accessforums.net/databas...try-21975.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    @aquabp,

    Would avoid autonumber as suggested by @ajax, just to keep things safe.

    Suggestion by @ajax in Post No #6 should work.

    At a basic level, the logic could be something like below ( Am very very rusty, so might have got things very very topsy-turvy ).

    Module :
    select * from tblClients where InvoiceNo = 0 order by DateTimeField, ClientID;
    rstInvoice0;

    Loop thro the rstInvoice0;
    select Max InvoiceID from tblClients;
    rstMaxInvoiceID;
    update tblClients set InvoiceNo = rstMaxInvoiceID + 1;

    End of Loop

    End of Module.

    Have a button on a form to call the above Module after your import.

    Thanks

  12. #12
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    you may not want to invoice everything that isn't invoiced. sometimes you have to wait. I also recommend making invoice numbers include dates. numbers are just great starting out. but down the road you may rather know that todays invoice 150416-003 was ran today and it was the 3rd that was run.

    rsData = currentdb.openrecordset("Select * from tblClient where InvoiceNo = 0 and Selected = true")
    rs.movefirst
    While not Rs.eof
    currentdb.execute("update tblClients set invoiceno = dmax("maxinvoiceid","tblclients","invoiceno=0")")
    wend

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It is not necessary to build date into invoice number to know the date of invoice - there should be an InvoiceDate field.

    My example does include year in the identifier but I did not design this structure - just had to deal with it. There are pros and cons to concept of building an identifier with meaning. Con is code is more complicated. Pro is that the number of characters in the identifier is static - we never exceed more than 4500 IDs each year - so don't have to make allowance for ID sequence number to grow to maybe 6 digits over the years, already have 30+ years worth of data and this ID structure has served all that time.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Ok it has been a while and I am still struggling with this loop. I am including the code that I barrowed and butchered. Also would like to know if there is an online source or a book that could walk me through the steps and define things.

    'NOTE: Every Month I download the same set of customers. I need to give them each
    'a new invoice number every month. I bill monthly and add work done in mass per day.

    'I want to create new invoice numbers using code instead of doing it manually.
    'Basically want to find Max Inv No then find first record with 0 in invoice column and update that 0 with the new invoice no, repeat until there are no more 0's in the invoice column
    'So far I have tried to use several example codes Like the one below but have had no success. Any help would be greatly appreciated!

    Private Sub Command3_Click()
    'Variables
    Dim dbsInvNo As DAO.Database
    Dim rst565_MowInvoice As DAO.Recordset
    'if I use another table to get the max Invoice does it need to be a variable? Dim rst500_EstimateS As DAO.Recordset
    Dim strSQL As String
    Dim intI As Integer
    'On Error GoTo ErrorHandler

    Set dbsInvNo = CurrentDb

    'Max invoice comes from the 500_EstimateS Table from the field Invoice. I have a query that finds the max invoice number and Adds one.
    ' I have not been able to get the DMax() function to work or at least show itself! Can I use the query in the code?


    If rst565_MowInvoice.EOF Then Exit Sub
    intI = 0
    With rst565_MowInvoice
    Do Until .EOF
    .Edit
    'How do I find a 0 value in the Invoice field in 565_MowInvoice table and change it to Max Invoice + 1. Max invoice found in 500_EstimateS
    .Update
    .MoveNext
    intI = intI + 1
    Loop 'Loop until there is no more 0's in the Invoice field of the 565_MowInvoice. This is about 300 records now.
    End With
    rst565_MowInvoice.Close
    dbsInvNo.Close
    Set rst565_MowInvoice = Nothing
    Set dbsInvNo = Nothing
    Exit Sub
    'ErrorHandler:
    ' MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    End Sub

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your code does not open recordset.

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [some table/query name here] WHERE some criteria here;")

    There are many books with code examples. Here is one: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc. I expect there is a later edition. Have to learn VBA language and syntax, apply logic to construct algorithms suitable to the situation.

    Consider:
    Code:
    Dim rs As DAO.Recordset
    Dim intI As Integer
    Set rs = CurrentDb.OpenRecordset("SELECT InvoiceNo FROM Invoices WHERE InvoiceNo = 0;")
    intI = DMax("InvoiceNo", "Invoices") + 1
    While Not rs.EOF
        rs.Edit
        rs!InvoiceNo = intI
        rs.Update
        rs.MoveNext
        intI = intI + 1
    Wend
    rs.Close
    Set rs = Nothing
    Last edited by June7; 05-07-2015 at 05:40 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-31-2015, 02:24 PM
  2. Create New Table with unique customer rows
    By jstopper in forum Access
    Replies: 2
    Last Post: 03-06-2014, 03:13 PM
  3. How to create same monthly invoices automatically
    By snehal0909 in forum Database Design
    Replies: 1
    Last Post: 05-09-2012, 06:33 PM
  4. Replies: 6
    Last Post: 04-27-2012, 05:14 PM
  5. Replies: 2
    Last Post: 12-20-2011, 07:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums