Automating Form Entry

[Purdue_Engineer]

I am interested in trying to automate form entry on a subform. I have searched around the web for a good answer but haven't had any luck.

The main form is "Customers" and each customer has a unique ID#, among other obvious attributes i.e. name, address, phone number, etc.

The subform is "Invoices". There is a "one-to-many" relationship established between each customer and his or her respective invoices. Invoice numbers are generated automatically.

Each customer is billed every quarter.

I am currently entering the invoice amount, invoice type (i.e. initial, quarterly, or final), and invoice date.

My goal is to create a tool which will automatically bill all customers in the database quarterly without having to input the redundant information for each individual customer.



I'm thinking that I will need to input the quarterly invoice amount once and then use that amount to automate all future quarterly invoices. I could make the invoice date be the current date.

I appreciate the help.

Thanks

[ItsMe]

Each Quarter a client generates a new invoice? Do you want to create an Invoice or a report that is a Statement?

A user defined function could include code to append a record to the Invoices table. You could have the UDF fire during Startup.

[Purdue_Engineer]

Yes. I'd like to create a new invoice for each customer and then print a report for each invoice.

I'm not well versed in creating UDF's. I'd like to use "Actual Removal Date" to determine which customer gets billed. For example, if customers A,B,C, and D are in the database and customer A has a value in the "Actual Removal Date" field, then only customers B,C, and D get billed (the field would be null for B,C, and D). Could you advise me on how to formulate that into a UDF?

Thanks for your help!

[ItsMe]

Then you should start by creating a query that will retrieve the clients that need to be invoiced. With that you could run an action query to append records to the Invoices table.

[June7]

What will be the source of data to use as a template for each invoice?

Maybe:

CurrentDb.Execute "INSERT INTO Invoices(InvoiceDate, CustomerID, InvoiceType, InvoiceAmount) SELECT #" & Date() & "#, CustomerID, InvoiceType, InvoiceAmount FROM tableORquery WHERE [Actual Removal Date] Is Null;"