Putting My Own Error Message on an Import if the Data is Incorrect

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a procedure that imports data from Excel using the "Transfer
Spreadsheet" command. However, the application that generates the Excel file
makes it entirely too easy to run the report for the wrong show, so I have a
check field in the spreadsheet and the table that will only allow the import
if this field has the correct data. In the table properties, I set the
Validation Rule property to check this field, and set up Validation Text to
explain exactly what went wrong so that the user knows exactly what to do
about it. However, when I tried to import test data with wrong information
in this field, I got the standard incomprehensible "unable to append" message
I usually get. The users of this database would find this singularly
unhelpful (I know I do). How do I get it to display my error message instead
of this gobbledygook?
 
Hi Amy,

One approach is to use TransferSpreadsheet to temporarily link to the
worksheet. You can then use DCount() to see if there are any "wrong"
records and if so tell the user what to do. If the data is OK, use an
append query to move it to your "real" table.
 
Back
Top