Best way to error-check rows in a table

[ExcessionOCP]

Hi,

I have a table that is populated from an excel import obtained from a separate system that uses different data types to my DB. This is then parsed by a function I have written which loops through the various values in the import table and and, using lookup tables, converts their IDs and imports them into my db.



This is working correctly, but often the entries in the import table are incorrect or missing.

I want to write an error-checking function but can't decide on the best way to achieve this.

My current thinking is:

For missing values - read each line from the import table and then check to see if any of the fields are null. If they are, add the field name to an array, then open up a form that will hold a bunch of combo boxes that allow the user to select missing values (this form will have combos for all fields in the table, some of which will be populated by the table data, others won't.

For incorrect values - for each field in the import table, verify that a valid lookup can be performed, and if not, open the form again to allow the user to replace the incorrect items with correct ones pulled from the various lookup tables.

Does this sound like a remotely practical plan? I fear that it's going to be really slow. Does anyone have any better ideas?

Cheers,

Phil

[Micron]

For 1) I think I'd open a recordset, applying the criteria as "...WHERE Field1 Is Null OR Field2 Is Null OR..." . If the rs count is zero, don't bother opening second form for adding values. If > 0 you can open the second form and base its record source on the same sql statement or pass the rs to the second form (don't think I have ever done that). If passing the rs is doable then you already have a rs that you can edit with the form controls. Or you could create the rs in a standard module and that way, either form should be able to work with the rs.

How do you identify 'incorrect' values? The 2nd approach might just be the same as the 1st.