Create Record Automatically

[Ken]

I do not have indepth knowledge of macros. I am creating
a small invoicing data base. I have created data bases
and a macro to allow me to input a month and automatically
print out invoices due that month (based on set reocurring
invoice amounts set in the database). I would like to
automatically generate a new record in a billing database
for each invoice printed. (e.g. I can print an invoice
for John Doe for $180. I want to update a billing
database with a new record for the $180 bill I just
created for John Doe.) I know I can create a data base
and physically enter in the new invoice, but how do I
automate it? Thanks for your input. Ken

 

[Steve Schapel]

Ken,

Any procedure that results in data being duplicated from one table to
another is not recommended, and would generally be regarded as invalid
database design. If I understand your question correctly, rather than
a separate billing table, you should add a field to the existing
table, either a Yes/No field, or perhaps a Date field, to record the
billing/invoice printing event. Then, you can use this in a query
whenever you need to access billing information. Please post back
with more details of your data and what you are trying to achieve, and
someone will be able to offer more specific advice if you like.

- Steve Schapel, Microsoft Access MVP

 

[ZenoParadox]

While I concur with Steve, a basic template for creating new records i
a table using DAO and VBA would look something like...


Dim rst as DAO.Recordset
Set rst = CurrentDB.OpenRecordset("YourTable")
rst.AddNew
rst!FieldName1 = "YourValue1"
rst!FieldName2 = "YourValue2"
rst.Update
rst.Clos

 

[Ken]

I will give a little more detail as Steve suggested. I am
developing a data base with multiple clients who are
billed once a year for an annual service. Customers are
billed durring different months, but the same customer is
billed the same month each year. The simple macro I wrote
asks me to type in the month. I opens a report, and
prints all invoices from a querry for that month.

My thought was to have the same macro update a billing
database with the date and amount invoiced automatically
after printint the invoice. Do you feel this is bad data
base design? My other alternative is to turn around and
key in the customer, date, and invoice amount after
printing the invoices.

What macro statements are required to accomplish the
automatic update?

Thanks.