how do I update a table automatically

[Don]

I have a database I want to update with information from excel automatically
but only if I choose to per record. So as I type a field in a form I want it
to find a matching field in the excel spreadsheet and automatically fill out
the rest of the fields in the form at which point I can edit the information
or use the selected information by pressing the *next record button. Any
ideas would be greatly appreciated.

 

[Graham Mandeno]

Hi Don

The best way to search for a corresponding row in an Excel worksheet is to
import the sheet as a linked table:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
<LinkedTableName>, <ExcelFilePath>, True, <WorkSheetName> & "$"

You can then open a recordset based on <LinkedTableName> and find the
required row (record) in the recordset using the FindFirst method.

Then you can update the local table with the Excel data extracted from the
current record of the recordset.

After you have finished, don't forget to close the recordset and delete the
linked table, or else the Excel file will remain locked.