Import Excel file into a mainform and subform

[icsupt]

I have an Excel spreadsheet that is updated every week. I would like to import this spreadsheet every week into an Access database. I know how to do this, but now I need to take this further.

When I import the spreadsheet every week, I am using the CustName field as the primary key. The CustName will change every week. So before I import the file into Access, I have to do a unique filter in Excel so that there is only one CustName.

Then in the same spreadsheet are the records that go with the CustName, i.e. custno, custaddress, etc.

Then I have the CustName in the mainform and have the corresponding information in a datasheet subform.

When I export the spreadsheet, I want to export two tables – one with just the unique CustName and one with all of the other information that will make up the subform. I am looking for a way to automate making the CustName a unique field in Access.

I hope I have explained this so that it makes sense.

Thanks in advance.

 

[dhookom]

Point of clarification: you don't import records into forms, you import records into tables. Records are displayed in forms.

I would import the spreadsheet into a temporary table and then do update or append queries to your regular tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]