Basically, I am creating a database to store performance data for several farms, and I have to pull the data from a records system (exported to excel) and import it into the Access db where I will compile reports from. I'm not sure if I have the db structure setup the way I need it, however. I have a table named Farms, which has fields for Farm Name, address, etc, and a table named PerformanceData that contains all the data imported from the records system. I have a field in the PerformanceData table called FarmID, and I created a relationship between FarmID and the ID field from the Farms table.
I have created a form where I can open a dialog box, chose the file, and then it will import into the PerformanceData table using DoCmd.TransferSpreadsheet. This all works fine. The question I have, is how do I set it up so I can pick from a list of farms in a drop-down so I can tell it which farm I want to import the data to. As is, when importing, the FarmID field is left empty, since there is no FarmID column in the excel file. I just need to be able to assign each record that is imported from the excel file a FarmID of 1, for example.
So, if I import the data, and manually put in the FarmID for the imported data. I get something like this:
Again, maybe I am approaching this entirely wrong. I do not have a ton of Access experience, so I welcome any thoughts.