Add Multiple Records w/ Command Button Click

[3link]

I have a table that tracks jobs I receive from clients (jobs table). Each job has various tasks, which I track with a separate table (tasks table).

I also have a third table that tracks 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 task#. I'm hoping this can be done with the click of a command button.

Can someone point me in the right direction?

 

[arnelgp]

how was your invoice# generated, what is the format?

 

[3link]

how was your invoice# generated, what is the format?

In my current setup, I have a control that counts the number of invoices I have produced for the year, and adds 1. It also adds the date and year. So it looks like this:

0216-040

This means the invoice was produced in February 2016, and it is the 40th invoice of the year.

 

[arnelgp]

it would be advisable to add index (duplicate not allowed) on your receivable table. invoice no+job no+task no.

the code would look like this:

dim db as dao.database
dim rs as dao.recordset
dim strInvoice as string
dim strTask as string
dim strJob as string
dim bolCreateNewInvoice as boolean

set db = currentdb
set rs=db.openrecordset("task table", dbopensnapshot)
with rs
if not (.bof and .eof) then .movefirst
bolCreateNewInvoice = True
while not .eof
'generate your invoice here
If bolCreateNewInvoice Then
strInvoice = yourInvoiceGenerator()
End If
strTask = !task.Value & ""
strJob = !Job.Value & ""
' insert record to receivable table
db.execute "INSERT INTO yourReceivableTable (Invoice, Job, Task) SELECT " & CHR(34) & strInvoice & CHR(34) & "," & CHR(34) & strJob & CHR(34) & "," & CHR(34) & strTask & CHR(34) & ";"

' if database returns 0, nothing is inserted since it will create duplicate
' record. if 1, then insert new record is successful.
bolCreateNewInvoice = (db.RecordsAffected <> 0)
.MoveNext
wend
.Close
end with
set rs=nothing
set db=nothing

 

[3link]

it would be advisable to add index (duplicate not allowed) on your receivable table. invoice no+job no+task no.

the code would look like this:

dim db as dao.database
dim rs as dao.recordset
dim strInvoice as string
dim strTask as string
dim strJob as string
dim bolCreateNewInvoice as boolean

set db = currentdb
set rs=db.openrecordset("task table", dbopensnapshot)
with rs
if not (.bof and .eof) then .movefirst
bolCreateNewInvoice = True
while not .eof
'generate your invoice here
If bolCreateNewInvoice Then
strInvoice = yourInvoiceGenerator()
End If
strTask = !task.Value & ""
strJob = !Job.Value & ""
' insert record to receivable table
db.execute "INSERT INTO yourReceivableTable (Invoice, Job, Task) SELECT " & CHR(34) & strInvoice & CHR(34) & "," & CHR(34) & strJob & CHR(34) & "," & CHR(34) & strTask & CHR(34) & ";"

' if database returns 0, nothing is inserted since it will create duplicate
' record. if 1, then insert new record is successful.
bolCreateNewInvoice = (db.RecordsAffected <> 0)
.MoveNext
wend
.Close
end with
set rs=nothing
set db=nothing

Thank you! I'll look at this more closely tomorrow when I have a fresh mind, but it seems to make sense.