Adding additional rows (3) to tbl2 based on tbl1 contents. - Please Help!! :(

[mesh1o]

Hi All,

I'm currently trying to identify an easier way of doing the
following task. If I try to explain the current process and where I want to go
hopefully somebody will be able to understand me and as a result think of a
faster way of doing it.

Three parts of the database one form and two
linked tables. The end result of the form is to create an output which can be
fed into accounting software as invoices (currently manually uploading them
through HTML website) this will throw out two csv files one with a summary
header (i.e. a customer name amount and invoice date) and the other with the
invoice details (invoice number and text for invoice) the two somehow merge
together to create an invoice.

This reduces the process time of 1 day +
manually raising 100+ invoices to a matter of minutes if it can
work...

The form consists of text boxes where user can input details
~(invoice requestor name, department, telephone number etc) then a subform which
is linked to table with invoice detail (text for invoice)

The process is
fill in all details about requestor (this will automatically create a new
invoice number, invoice date and due date) this will populate in table one. The
subform (consists of 6 lines) contains invoice line details and amount, here you
can enter the invoice details and the amount required (if multiple lines require
multiple amounts this is possible)

Then clicking next record populats the
two tables linked by the invoice number. I can go ahead and export and upload
into the accounting software. However, this process still requires the invoice
to be typed out in access ready for the upload.

my query is the
base invoice details (table one) will be consistent throughout the entire


process so I wouldn't need to change that, I would like to create a macro to
create this table 2 with the required fields so that I do not need to reenter
everytime. For example, the description is going to be the same for each of the
100 invoices and I will put a contact email in the description as well. So what
I'm asking for is if it's possible to have a macro that once table 1 is
populated then for each invoice create three additional rows in table 2 and
populate with for example:

TABLE ONE
row1: 1 CH001 Marianne £4050 29/03/14 Mesh
row2: 2 CH002 Mark £3400 29/03/14
row3: 3 CH003 Suzie £123 29/03/14

TABLE TWO
row 1: 1 CH001 invoice for £4000
row 2: 2 CH001 invoice for laundry £50
row 3: 3 CH001 any queries please contact
row 4: 4 CH002 invoice for £3400
row 5: 5 CH002 invoice for laundry £
row 6: 6 CH002 any queries please contact

so for every new invoice (CH001, CH002) in
table 1 add three lines for that invoice in table 2


I hope that makes sense it probably doesn't lol

Thanks for any assistance and for taking time to help me

[June7]

Code can create the 3 records but how will Access 'know' to split up the invoice amount as shown for CH001? This is accomplished by data entry. The total of invoice items should not be saved into table 1, it should be calculated when needed.

The third record isn't even appropriate for saving in table 2.

If the ultimate goal is to create a CSV file, don't try to create records in table that mimic the CSV structure. Build proper relational tables and use query and/or VBA code to export data to CSV file.

[mesh1o]

hi thanks for your response

attached is a pic of the two tables, I also have a linked excel file which contains the following three columns 1) customer number 2) invoice amount 3) customer description

I have linked the table and from that used update queries to pull that data into the 'invoices table'. all the person fields are predefined and won't change, the excel append will add to fields CustomerNo, Net and Total (Vat is 0), from that I then want the three lines to add into invoiceitems table. With the first line to be the value from invoices table (if that helps)

for this exercise I don't need the value split over multiple lines just on the first one.

hope that makes it a little bit clearer

I should have added: the structure for the csv file isn't as per below but it uses some of those fields. If you would like to see the csv structure for export then I can take a picture of that

thanks
thank you

[June7]

Doesn't really contribute any new info. My comments are same.

The Invoices table does show a Total field which I stated should not, assuming each invoice can have multiple line items with individual amount, the sum of which is the total for the invoice.

I don't understand "I don't need the value split over multiple lines just on the first one.", when the original example clearly shows the total of CH001 split to multiple records in table 2. I also see that CH002 does not show split. This is just more confusing.