Results 1 to 2 of 2
2001-04-04, 10:21 #1JamieTGuest
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.
2001-04-05, 07:48 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 187 Times in 173 Posts
Re: Access 2000 automatically create new record
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.Regards,
Microsoft MVP - Excel