Importing excel data into table automatically

[jwalker55]

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.

[June7]

You import one farm's data at a time so all the new records of import would be for one farm? After the import run an UPDATE query to populate these new records with the FarmID.

In some event (button Click, combobox AfterUpdate), run code (I use only VBA):

DoCmd.RunSQL "UPDATE tablename Set FieldID=" & Me.comboboxname & " WHERE FieldID Is Null"

[jwalker55]

You import one farm's data at a time so all the new records of import would be for one farm? After the import run an UPDATE query to populate these new records with the FarmID.

In some event (button Click, combobox AfterUpdate), run code (I use only VBA):

DoCmd.RunSQL "UPDATE tablename Set FieldID=" & Me.comboboxname & " WHERE FieldID Is Null"

Exactly, that sounds like it might work. I will give it a try.

Thanks!