Recurring Invoices

[rnickels]

Hello,

Thank you for your time.
I want to set up an automated recurring monthly invoice for clients in my database.

I know this is possible but I am having trouble finding the solution and in fact having problems how to search for a solution.

On the 1st of every month I would like to have a query run that inserts a new row into my invoice table for all current clients invoicing them for that month.

Please please help. Invoicing one at a time using a form is taking far too long each month.

Thank you.

 

[winshent]

Write some VBA that gets triggered from AutoExec which checks the system date. If 1st of the month then loop through all of your clients and create your invoices..

Easy..

public sub CreateInvoices

if not FirstDay then exit sub
dim db as dao.database, rs as dao.recordset, sql as string

SQL = "SELECT ClientID FROM tblClients"

set db = currentdb
set rs = db.openrecordset(sql,dbopendynaset)

rs.movefirst
if not rs.EOF and not rs.BOF then
do until rs.eof
' code to create invoices goes here
rs.movenext
loop
end if
end sub


Function FirstDay() As Boolean
 
  If day(Now()) = 1 Then FirstDay = True

End Function

 

[rnickels]

thank you for your time...in the vba code you say "code to create invoices goes here"
Can you give me an idea of sample code.
Thanks again

 

[winshent]

sub CreateInvoice(ClientID as long, description as string, InvoiceNumber as string)
dim db as dao.database, rs as dao.recordset, sql as string

SQL = "SELECT ClientID, InvoiceNumber, Description, Date FROM tblInvoices"

set db = currentdb
set rs = db.openrecordset(sql,dbopendynaset)

rs.addnew
rs!ClientID = clientid
rs!invoicenumber=invoicenumber
rs!date=now
rs!description = description
rs.update

end sub