auto create new data

[Newbie]

Hello,

I have a simply invoice system using access database.
I will have only one invoice for each customer every month.

i.e.
I have 1000 customers.
I bill these 1000 customers $5 every month.
Can I program this to one command i.e. "new month"? This "new month" command
will auto create new database, all customers will have a new invoice for new
month.
Otherwise, I have to create one by one for these 1000 customers.

Thanks

 

[Allen Browne]

Use an Append query to create the new invoices.

1. Create a query into your Customers table.

2. Change it to an Append query (Query menu).
Answer that the Invoice table is the one to add to.
Access adds a new row to the grid.

3. Drag the CustomerID into the grid.
It should map to the CustomerID field in your Invoice table.

4. In a fresh column in the field row, enter:
Date()
In the Append to row, map this to the InvoiceDate field.

5. In the next column, enter:
5
and map this to the Amount field.

6. Save the query.

When you run the query, it will create a record in the Invoice table for all
customers, dated today, for $5.

Chances are that what you actually need will be more complex than that, but
that should get you started. For more complex scenarios where you have the
Invoice and Invoice Detail tables, where you are looking up the amounts each
customer owes, and combining all the costs for the month into a single
invoice, you typically have to OpenRecordset in code, and AddNew and Update.