VBA to populate second table based on table 1

[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

 

[robert_mann]

VBA is a great thing. Some people do all the accounting jobs with excel and access. But for me I never used this manual things for my jobs or invoicing. I don't feel it really professional. So for [Link Removed]. One of the nearly free software still available on the net. Guncash is another one but I don't like its interface.

 

[Cronk]

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

So if you have
CH001 Marianne £4050 29/03/14 Mesh

and you want to add to another table
row 1:1 CH001 invoice for £4000
row 2:2 CH001 invoice for laundry £50
row 3:3 CH001 any queries please contact

How do you work out the split of £4050 into the two separate amounts and how do you determine that one amount was for laundry?

No, you haven't explained it clearly.