In
BeWyched said:
Hi Dirk
Many thanks for your interest in this.
The following code (I've chopped out the meaty bits leaving the
relevant coding) is just 1 example.
The coding runs the first time a User runs a new upgrade. It tests to
make sure the last-but-one upgrade was installed (this created a new
Table called AssetRiskQuestions) and also the last upgrade which
added a new Field (EntryDate) to the above table. It does this by
trying to run a SQL query against the table (if it Errors the table
is then created). If the table exists, i.e. no error, then it tries
to read the EntryDate field and creates the field if the system then
errors.
Accordingly, if the User didn't apply the last-but-one upgrade, the
sequence of Error commands will be:
On Error GoTo Err_Hnd
On Error GoTo crTable
On Error GoTo Err_Hnd
If he applied that upgrade but not the last, the sequence will be:
On Error GoTo Err_Hnd
On Error GoTo crTable
On Error GoTo crField
On Error GoTo Err_Hnd
As I mentioned, this works fine except it seems to drop the final
Error statement in the second example but does not preserve the
previous one resulting in a run-time error corruption if the system
then errors. i.e it seems to abondon all error capture. I can test
this by stepping through the code using 'Stop' and F8's.
I hope this makes sense!
Cheers and thanks again for your interest.
BW
Sub Starts Here
On Error GoTo Err_Hnd
' Coding Block 1
On Error GoTo crTable ' Test for the existence of the
AssetRiskQuestions table by selecting a known field
Set rst5 = db2.OpenRecordset("SELECT Q1 FROM AssetRiskQuestions")
Set rst5 = Nothing
On Error GoTo crField ' AssetRiskQuestions table exists but
does the EntryDate field exist
Set rst6 = db2.OpenRecordset("SELECT EntryDate FROM
AssetRiskQuestions") Set rst6 = Nothing
GoTo crLink
Exit Sub
crTable:
On Error GoTo Err_Hnd
Coding Block 2 - creates Table
GoTo crLink
crField:
On Error GoTo Err_Hnd
'Coding Block 3 - creates Field
crLink:
On Error GoTo Err_Hnd
' Coding Block 4 - Create/recreate link to Table
Exit Sub
Err_Hnd:
MsgBox Err.Description
End Sub
As far as I can see, you're never using the Resume statement to exit
your error-handlers. You're just using GoTo statements to jump hither
and yon. This is not the correct way to implement error-handling, and
I'm not surprised that VBA is getting confused. Each error-handler
should terminate with one or another form of the Resume statement.
Some people would say that you shouldn't be using error-handling to trap
errors that you could avoid by preliminary testing. I don't altogether
agree, since sometimes error-trapping is the easiest way to check for
certain conditions. However, you shouldn't necessarily assume that any
error encountered is the one you expected. better to check error
numbers in your error-handlers.
Let me see if I can put together an alternate version of your routine.
This is air code:
'------ start of revised code ------
Sub YourSub()
On Error GoTo Err_Hnd
Dim rstCheck As DAO.Recordset
Dim lngErrNo As Long
Dim strErrDesc As String
' Coding Block 1
Check_Table:
' Check for existence of AssetRiskQuestions table,
' create it if necessary.
On Error GoTo crTable
Set rstCheck = db2.OpenRecordset("SELECT Q1 FROM
AssetRiskQuestions")
On Error GoTo Err_Hnd
rstCheck.Close
Check_Field:
' Check for existence of EntryDate field,
' create it if necessary.
On Error GoTo crField
Set rstCheck = db2.OpenRecordset("SELECT EntryDate FROM
AssetRiskQuestions")
On Error GoTo Err_Hnd
rstCheck.Close
crLink:
' Coding Block 4 - Create/recreate link to Table
Exit_Point:
Set rstCheck = Nothing
Exit Sub
crTable:
lngErrNo = Err.Number
strErrDesc = Err.Description
On Error GoTo Err_Hnd
If lngErrNo = 3078 Then
' create the table, with the new field
db2.Execute "CREATE TABLE ...", dbFailOnError
Resume crLink
Else
' Raise the original error again
Err.Raise lngErrNo, , strErrDesc
End If
Resume Exit_Point ' shouldn't be executed
crField:
lngErrNo = Err.Number
strErrDesc = Err.Description
On Error GoTo Err_Hnd
If lngErrNo = 3061Then
'Coding Block 3 - creates Field
Resume crLink
Else
' Raise the original error again
Err.Raise lngErrNo, , strErrDesc
End If
Resume Exit_Point ' shouldn't be executed
Err_Hnd:
MsgBox Err.Description
Resume Exit_Point
End Sub
'------ end of revised code ------
