Solved: Importing into 2 tables. & read columns...

[ViolettePhan]

Hi all,

I'm new here and also newbie to VBA. Please help me out...

I have 4 tables:
tbl_Address (AddressID(autonumber), Company, title, address, city...)
tbl_X_Ref_Address (AddressID, CategoryID, SubCategoryName)
tbl_Category (CategoryID, CategoryName)
tbl_Ref_SubCategory(CategoryName)

My requirement is to create a form to import external address (mostly in excel format) into the tbl_Address, it should read the columns in the import file and allow user to match them with the corresponding column in tbl_Address.
So I created a form: an option for user to open file dialog to pick their excel file location, a list box of Categories lstCategory, and combo box for SubCategories cmbSubCategory. & a command button cmdImport. So far I can code up to just import (append) the data into tbl_Address, but I need to insert those new record from tbl_Address to cross reference table tbl_X_Ref_Address with those new AddressID, CategoryID and SubCategory.

Can anyone please show me how to do it... I'm hitting the wall right now... I haven't figure out how to give user the option of pick columns yet but I can save it for later work. Right now I just want to see how to import to cross reference table first...

Many thanks

Violette

[OBP]

Violette, do the Excel files and your import function have the same format each time?
i.e. is all the data for all the tables present?
If it is you should import all the data in to one table and then be able to write simple append queries to first append the Address data to the address table, then a query using the AddressID to to append the X_Ref_Address table. Then the Category table then the SubCategory table.
It would obviously help if you could attach some typical excel files and the Detabase, even with no data in to this post.

[ViolettePhan]

Hi OBP,

Thanks for quick reply.
I can insert the excel file to tbl_Address. My problem is if user pick a category or/and subCategory, then the AddressID, CategoryID and SubCategoryName should enter into tbl_X_Ref_Address...

The excel file not always come in as same format as table tbl_Address(that's why the requirement need to read the columns and matching it with tbl_Address), but I have to make excel file have the same format as tbl_Address right now so I can test insert first... Onething at a time first...

I attaching the import form and tables + excel file... Please take a look and help me out...

Thanks very much for for time and help...
Violette Phan...

[ViolettePhan]

Thanks OBP. Your're the best of the best!!!!