I have a table that tracks jobs I receive from clients (jobs table). Each job has various tasks, which I track with a separate able (tasks table).
I have a third table from which I track billings (receivables table). When I bill clients, I bill for every task associated with a particular job at once. Each task is identical, so they are all billed at the same amount. However, my clients prefer separate invoices for each task.
I'm trying to write a code that will populate the receivables table with a separate row for each task so I can generate separate invoices from that. Each row would be almost identical with the exception of the invoice# and taskID. I'm hoping this can be done with the click of a command button.
Ideally, the receivables table would look like this.
There has to be some variety of the "Insert into" function that provides for this. But I can't quite figure out how to program it to populate rows on the receivables table commensurate with the number of tasks for a particular job.
Another obstacle I will face is generating the invoice number. Under my current system, the invoice number is generated by a form control that calculates the number of invoices billed within the year. Then it adds the month and year. For instance, 0216-040 means that this is invoice was created in February 2016, and it is the 40th invoice this year.