Error Trapping

[Prado01]

G'day all,

I am very new to VBA programming have have basically been teaching myself as I go.
I have created a function that will import text files automatically on the opening of the DB. One of the files that is import automatically is only created once a month but the DB is open atleast once a week. I have tried to create an error handler that will show a msgbox when this file "Audit.txt" can not be found but continues on to the next Docmd in the function. Has anyone got any examples of how I can do this.

I have attached the code for the function to import the text files in.

Cheers

Prado01

DoCmd.TransferText acImportDelim, "AuditImportSpecification", "Nursing Audit", "M:\audit.txt", True
DoCmd.TransferText acImportDelim, "ClinicalindicatorsImportSpecification", "Clinicalindicators", "M:\clinicalindicators.txt", True
DoCmd.TransferText acImportDelim, "ProceduresImportSpecification", "Procedures", "M:\procedures.txt", True
DoCmd.TransferText acImportDelim, "VentdataImportSpecification", "Ventdata", "M:\ventdata.txt", True
DoCmd.TransferText acImportDelim, "OsmoFloImportSpecification", "Osmoflo", "M:\OsmoFlo.txt", True

 

[Laurentech]

What's the error number that pops up when the file isn't found?

Larry

 

[Prado01]

Run time error '3011':

 

[Laurentech]

OK, so put the following code in the function where you have the transfertext stuff:

Funtion ImportTextFiles() --this would be whatever your function name is
On Error Goto Err_ImportTextFiles

*your transfertext stuff goes here*

Exit_ImportTextFiles:
Exit Function

Err_ImportTextFiles:

Select Case Err.Number

Case 3011

*do something here if you want to log the error
*don't use a msgbox as it will stop the code and wait for the
*user to click OK

Resume Next

Case Else

Resume Exit_ImportTextFiles

End Select

End Function


This should ignore the file not found error and continue on to the next transfertext command. Other errors will be handled as they were before.

Larry

 

[Prado01]

Thanks Lauren can you tell me how I can use this to identify which text file is causing the error, say if the audit.txt file is there but another file is missing?

Cheers
Prado01

 

[Laurentech]

Sure - right after the Case 3011 line you could save Err.Description to a memo field in a table. Let me know if you need details on how to do that. I'm no expert programmer, but I'm sure we could come up with something that would work for you.

Larry

 

[bonekrusher]

Try downloading MZtools. Its a freeware VBA addon. Its has an error handler that really is good.