I am looking for a way to create a button on a subform that will perform a batch import of data into the subforms table that is specific to each record in the table of the parent form.
Let me explain.
I have two tables: tbl_Accounts and tbl_ServiceLocations.
In the tbl_Accounts table I have the following fields:
AccountID (PK)
AccountName (Short Text)
AccountPhone (ShortText)
AccountFax(Short Text)
AccountBillingStreet1 (Short Text)
AccountBillingStreet2 (Short Text)
AccountCity (Short Text)
AccountState (Short Text)
AccountZipcode (Short Text)
In the tbl_ServiceLocations table, I have the following fields:
ServiceLocationID (PK) (Autonumber)
AccountID (FK ) (number)
LocationName
LocationStreet1
LocationStreet2
LocationCity
LocationState
LocationZipcode
I have created a 1 to many relationship between the two tables using the AccountID and stipulating "Enforce Referential Integrity" & "Cascade Update Related Fields." I also chose option number 3 under Join type.
I have create a form from the tbl_Accounts table and placed a subform inside that form using the tbl_ServiceLocations table. On the subform (it is a tabbed subform-dont know if that makes a difference) I have linked the Master and Child fields in the data property sheet to the AccountID in the parent form.
Everything works fine. I can flip through each of the tabs on the sub form and enter data which will be automatically tied to the tbl_Account table by AccountID. Specifically meaning, I can add a service location (Location Name, Street Address, City...blah blah blah) that is directly related to the specific Account Name inside the tbl_Account table. Again, everything works just fine.
However, I have some accounts that have 100's even thousands of service locations. So my question is, how do I place a button on the "ServiceLocation Subform" to give the use the option to do batch import of many service locations into just that one specific Account name?
Finally, I have two spreadsheets. 1. tbl_Accounts which populates the data in that table just fine. 2. I also have another spreadsheet with Account Name, Location Name, Street Address...blah blah blah.. that works just fine in the service locations table.
One caveat here - the Account Names in the tbl_Account Table spreadsheet & the Account Names in the tbl_ServiceLocations spreadsheet differ slightly (this is due to the export process of the main data source)
Any advice or guidance is always appreciated.
Thanks!