Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-29-2014, 09:20 AM   #1
mesh1o
Newly Registered User
 
Join Date: Mar 2014
Posts: 12
Thanks: 6
Thanked 0 Times in 0 Posts
mesh1o is on a distinguished road
VBA to populate second table based on table 1

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

mesh1o is offline   Reply With Quote
Old 07-02-2017, 09:57 AM   #2
robert_mann
Newly Registered User
 
Join Date: Dec 2016
Posts: 18
Thanks: 2
Thanked 1 Time in 1 Post
robert_mann is on a distinguished road
Re: VBA to populate second table based on table 1

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.

Last edited by CJ_London; 07-02-2017 at 01:40 PM.
robert_mann is offline   Reply With Quote
Old 07-02-2017, 10:20 PM   #3
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,846
Thanks: 2
Thanked 396 Times in 391 Posts
Cronk will become famous soon enough
Re: VBA to populate second table based on table 1

Quote:
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.

Cronk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Populate based on another table Viper210 Tables 3 05-11-2012 12:08 PM
Auto populate based on what is in the table dcollard23 Forms 2 10-13-2009 07:09 AM
Populate yes/no based on what was inputted into table dcollard23 Forms 0 09-09-2009 11:36 AM
auto populate field based on other table rashidzaib Forms 3 10-07-2007 10:16 PM
[SOLVED] Pre-populate table based on parameter MikeTheBiff Macros 0 07-25-2002 02:10 AM




All times are GMT -8. The time now is 03:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World