Automatic invoice creation - append query?

[triplee23]

Hi,

I hope someone can help me with my Access 2010 invoicing solution. Here is the information:

I am building a traditional invoice solution with a table for the invoices and related table with the order items. I have a table with all the customers and here comes the issuel. The customers have pre-defined products that they buy and I would like to mass create the invoices. The products are in the products table.

So I have four tables:

tblCustomers (also containing the information on which products the customers buys)
tblProducts
tblInvoices
tblOrderItems

So in words I would like to:

For every customer, create the invoice for every customer, and add the relevant products (one or more) to tblOrderitems (with the correct invoice foreign key to make the relationship work).

I would like this to happen with a macro, just pressing a button in a form.

I have tried the ForEadhRecord logic but this block does not accept creating new records. I have looked at some kind of append query but have not succeded. I succed in creating one invoice per customer just running an append query, but how do I add the order items and populate them with the right foreign key so that the order items are related to the correct invoice?

I hope someone can lead on the right track.

Thanks in advance!

Triplee23

 

[triplee23]

No response, I programmed this in VBA instead.