Results 1 to 2 of 2
  1. #1

    Access 2000 automatically create new record

    Ok, this one is going to be slightly difficult to explain but I'll try.

    I have set up an Access 2000 db to manage my customers and billing. When I create an invoice it often needs to be re-invoiced the following year as an on-going service.

    Each invoice record has a field where it is set as to whether it will be re-invoiced or not (Radio button set for Auto,Manual,No) so I currently have the ability to manually list each days re-invoices due.

    On each invoice record there are some fields that would need to be reproduced automatically in a new invoice and some that have yes/no check boxes as to whether that item in the invoice is to be re-invoiced.

    In order to know when the invoice is to be reproduced the following year I have a [Duration From] field which inserts a date into a [Duration To] field for one year later minus one day and also sets a [Reinvoice Date] for one month behind the [Duration To] date. (Are you still with me?!)

    So, in short I have the ability to set an invoice reproduction event based on [Reinvoice Date] equal to today's date.

    The real problem is to be able to automatically re-produce the old invoice ensuring that appropriate items are re-created. (Not forgetting to change the Reinvoice status of the old invoice to NO)

    I think I better leave it at that for now rather than confusing you, the poor reader, any further!

    Any suggestions, helpful hints, or directions to the exit door would be appreciated.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts

    Re: Access 2000 automatically create new record

    Hi Jamie,
    I guess there are two ways you could approach this. First, you could create the second invoice at the same time as you create the first one (on saving a record, check if reinvoice is set to auto, if so, copy the required fields then loop through the optional ones verifying if their relevant checkboxes are checked), but only print (or email or whatever) the current one. You'll then have to run a daily report of invoices to go out. Second you can run a daily query for all records that have a reinvoice date equal to the current date and then loop through each of those, creating a new record with the appropriate fields.
    Either way you're going to need daily reports/queries and some code, but I guess the first might be more useful if you wanted to project/estimate future income.
    Hope that helps.

    Microsoft MVP - Excel

Posting Permissions

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