Error trap in function called by another mod.

[nathan_savidge]

Hi

I have a module that imports data from a spreadsheet, a row at a time. The
import needs to be done like this, i wont go into why.

I have a function, which modifies dates, called UK_DATE(dtDate as date) this
is called in the part of the mod that build the sql, i.e.

SQL = "INSERT INTO..............UK_Date(xl.range("B" & colcount))......."

I get errors when the dates are incorrect, i.e. 1/1/2/07 instead of 1/12/07.
I have an error trap in the import function, but it does not detect errors
when they are in the UK_Date function. Can you advise as the error trap
needs to note in a text file the name of the sheet and the row it failed on.

Thanks

 

[Klatuu]

It would be interesting to know why you think it is necessary to import one
row at a time. I would suggest importing into a table with that column
formatted as text.
Then I would use an append query that calles a function that would analyse
the field and either return the date if it is correct or a null if it is not
correct. This function would also append to another error table the primary
key of the row and the value of the field that could not be converted to a
date.

 

[Jim Burke in Novi]

ASsuming you have a loop where you proces the rows and that you want to
continue with the next row after an error occurs and is handled, something
like this would work (I'm using 'pseudo-code' to denote the loop and the
reading of the next row since I don't know how you have this implemented)

Get first row ' (however you're doing this)
while not end of file
on error goto ProcessError
INSERT ...
goto GetNextRow 'if there was no error then just get the next row
ProcessError:
' do whatever you want to do to handle the error here, then get the next
row
GetNextRow:
get next row '(however you're doing this)
Wend