Results 1 to 15 of 15
  1. #1
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10

    Auto update

    I have an Orders form with a Orders Detail subform. I have added a status Yes/No field so that the user can select which items can be invoiced.
    Clicking on the invoice button will create an invoice report for that record only. Once invoiced, I need to have a 2nd Yes/No (named Invoiced) be automatically updated to YES so that the same line is not invoiced at a later date.


    How can I make this happen?

    I started using Access in Version 2.0 but havent used it for 20+ years.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Instead of the yes/no field,I suggest a date field.
    this tells you when it was invoiced and provides the YES.

    you would run an update query using the Invoice# as criteria.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Perhaps instead of a Yes/No field for Invoiced, use a Date/Time field and update with Date(). That way you kill two birds with one stone.
    You know it has been invoiced as filed is not Null and you also now know when?

    You would use an Update query when you know they items have been invoiced.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    . Once invoiced, I need to have a 2nd Yes/No (named Invoiced)
    alternatively suggest this should be a field to store the invoice number not a yes/no field.

    as to how to make it it happen depends on how your are creating your invoices - one at a time or as a batch run once or more times a day.

    Using something like a 'create invoice' button is probably the easiest but you need to account for issues that you may come across such as printer failing or the code encountering an error. So clearly define what it means by 'created'. You've set the process running, invoice printed successfully or pdf/whatever created or perhaps once the invoice has been successfully emailed or put in the post tray.

    if a batch run, if the invoice is generated as a report, you might be able to use a report event to update the table or use a data macro in the invoice line table

  5. #5
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10
    I've got the front end working (taken me a month to figure out and is probably very messy compared to most of your db's) and I will create a report for the invoice. I'm not sure if I need to create a form for Invoice as the report is the invoice. I defo need a table to generate the invoice number but at present, cannot figure out the way forward.

    As not all works or costs will be complete, for example material costs will be added later once the suppliers have sent them through, only the main works will be invoiced as a single invoice, followed by another invoice either the same month or the following month. This is why a tick box is included so that they can select what is to be invoiced on that particular PO.

    What i'm stuck on is how to create the query to generate the invoice and update a field to show it's invoiced. I like the date idea although when I changed from Yes/No to date, it populated the date field with 30/12/1899.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    That has just converted the value of the boolean to a date.
    Just run an update to set the date field to Null
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10
    Quote Originally Posted by Welshgasman View Post
    Just run an update to set the date field to Null
    Easy for you to say! how do I run an update to do that? Hard to think I created a fully working Fantasy Football DB that allowed for substitutions and everything else including who had paid that week! I'm getting too old for this.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    It would help if you can upload the database so we can see the table structures.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10
    Can't seem to upload so have attached dropbox https://www.dropbox.com/s/eaed3q4yi4...ers.accdb?dl=0

    Looking through Northwind to see where I'm going wrong.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    So for the Job listed in the screenshot attached you have:-

    Purchase No 1
    Customer Job No 35341

    With 3 requirements listed.

    Would you send an Invoice for each requirement as a separate Invoice or would you send an Invoice for All 3 Items?
    Attached Thumbnails Attached Thumbnails Job.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10
    That screen needs to be re-worked.

    If you go to Orders List and click in Purchase No and then click on Open Record, that record has the correct tick box. So if eg record 6 has the Utopia doors has been completed, we can invoice just that part. If all jobs for that record have been completed, we can invoice all 3.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    OK So in the attached Click on the Order List Form - Open required Record.

    Then enter Date Invoiced and then click on the Command Button Preview Invoice.

    The Report produced needs some work but does what you want.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by screech63 View Post
    Easy for you to say! how do I run an update to do that? Hard to think I created a fully working Fantasy Football DB that allowed for substitutions and everything else including who had paid that week! I'm getting too old for this.
    Use the query wizard as it is a one off issue
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10
    Thanks mike60smart. That works but I need to have it produced with the invoice number. Not sure how to tie that it.
    Also, wanted the tick box so they can just click to select rather than inputting the date. Clicking on the preview/invoice would then generate the date in the INVOICED field.

  15. #15
    screech63 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2022
    Location
    UK
    Posts
    10
    Quote Originally Posted by Welshgasman View Post
    Use the query wizard as it is a one off issue
    Thanks Welshgasman, after looking around found the update query option

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

Similar Threads

  1. Auto update student's age
    By raywhite in forum Access
    Replies: 5
    Last Post: 12-30-2020, 02:17 PM
  2. Auto update combobox
    By omegads in forum Access
    Replies: 21
    Last Post: 09-07-2016, 06:20 PM
  3. Auto update reference
    By cbende2 in forum Programming
    Replies: 8
    Last Post: 12-31-2014, 12:50 PM
  4. Auto Update Question
    By LionsCricket in forum Access
    Replies: 3
    Last Post: 04-11-2013, 03:22 PM
  5. Auto Update Forms
    By Vikki in forum Forms
    Replies: 1
    Last Post: 02-09-2010, 10:51 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