Importing Record with Multiple Subform/Child Records

[teamcooper]

Hello!

I am working on a db that records employee reimbursable expenses. I'd like the employee to fill out either an online form or email an excel sheet (from a template), the resuts of which will be imported to populate a form which the administrator will review and add to the database.

The problem is that each expense report can have multiple lines written to different tables. Here is very simplified example

tblVoucher (Master)

VoucherID	EmployeeID
22	3

tblTravelExpenses (Child)

DATE	VoucherID	REASON	KM
12/12/14	22	Education	100
13/12/14	22	Conference	200

tblReimbusements (Child)

DATE	VoucherID	VENDOR	TOTAL
12/12/14	22	Staples	20.00
10/12/14	22	Taxi	10

So my question is, how do I approach preparing the data for the import and then successfully populating the form?

Thanks!

[teamcooper]

Ain't that the way -- I think I got a clue right after I posted. Should I be looking at something like this (import xml)?

[burrina]

Why not simply use an existing inside db approach for this?

[ItsMe]

...the resuts of which will be imported to populate a form...

Data from the outside would be imported to the tables. It is important to keep this in mind as you figure out a way to import data to your DB.

So my question is, how do I approach preparing the data for the import and then successfully populating the form?

If you have a way to control how the data is created, be sure to include a key value in each row created that represents a given VoucherID. If you have a VoucherID for a given row in a spreadsheet, etc., appending child tables should not be an issue. After you have appended your tables, use a query to retrieve the records and display the results in a form.

[June7]

Also, review http://office.microsoft.com/en-us/in...001103247.aspx

Access 2007 and 2010 have a utility called Collect Data (at least I can't find any reference that indicates it is in earlier versions). However, support for that utility was partially removed in Access 2013 (can't create new setups).

[teamcooper]

Thanks guys! Sometimes I don't even know where to begin. I appreciate the tips!

[ssanfu]

Another option...

Once the spreadsheet is reviewed, it could be saved as a CSV file.
Then VBA could be used to open/read the CSV file and append the data to the correct tables.