Error handler

[Rob.Mills]

Has anyone ever had their error handler not work the way it should.

I've got a sub that I've declared at the top On Error GoTo Err

At the bottom I've got Err:

And I have a specific action to take based on err number 3021.

But when I run the sub it still gives me the plain old error message with the debug button saying err number 3021.

Kind of weird.

 

[Mile-O]

Just so you know, Err is an Access object.

Try renaming your error handler to something like:

On Error Goto Err_Hander

Err_Handler:

 

[Shadez]

hmm i think i c ur problem... Editam beaten to it



err is a function and your also using it as a error call, try just changing the name of the error handle

ie

ErrHdl instead of Err


are you turning the error handler off when you leave the sub?


Post ur code here for a more detailed analysis



ShadeZ

 

[Rob.Mills]

Tried switching it to ErrHandler but still had the same problem.

How do you turn it off?


Public Sub fInternal(dtmStart As Date, dtmEnd As Date)
'Exports Checks queries to Internal excel template

On Error GoTo ErrHandler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstData As DAO.Recordset, rstSheets As DAO.Recordset
Dim xlApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strSQL As String, strQuery As String, strSheet As String
Dim intRow As Integer, intColumn As Integer, intField As Integer, i As Integer

'Create SQL Statement to filter names of queries to use out of tblQueries
strSQL = "SELECT tblQueries.txtQuery, tblQueries.txtLabel, tblQueries.txtDates " _
& "FROM tblQueries " _
& "WHERE (((tblQueries.txtFilter)='Internal'));"

'Set db to current database
Set db = CurrentDb

'rstSheets will contain the names of the queries, worksheets
'and whether or not it needs the parameters
Set rstSheets = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Create the excel application
Set xlApp = CreateObject("Excel.Application")

'Create the workbook based on a template
Set XLBook = xlApp.Workbooks.Open("J:\Tax\Outsourcing\CP\Cash Processing Internal.xlt")

'Start at beginning of list of queries
rstSheets.MoveFirst

'Perform on all records
Do Until rstSheets.EOF

'Save names of query and worksheet for later
strQuery = rstSheets!txtQuery
strSheet = rstSheets!txtLabel

'Point to correct querydef
Set qdf = db.QueryDefs(strQuery)

'If this query has parameters they will be set here
If rstSheets!txtDates = True Then
qdf![[Forms]![frmDateSelector]![txtStart]] = dtmStart
qdf![[Forms]![frmDateSelector]![txtEnd]] = dtmEnd
End If

'Open a recordset based on query
Set rstData = qdf.OpenRecordset(dbOpenSnapshot)

'Point to correct worksheet
Set XLSheet = XLBook.Worksheets(strSheet)

XLSheet.Cells(3, 1) = "Week Ending " & dtmEnd

With rstData

'Start at first record
.MoveFirst

'Setup initial counting variables
intRow = 6
intColumn = .Fields.Count

'Perform on each record
Do Until .EOF

'Initial field to start at
intField = 0

'Perform on each field
For i = 1 To ((intColumn * 2) - 1) Step 2

'Set the value of the correct field
XLSheet.Cells(intRow, i) = .Fields(intField)

'Prepare to advance to next field
intField = intField + 1

Next i

'Prepare to advance to next row
intRow = intRow + 1

'Go to next record of data
.MoveNext

'Repeat!
Loop

End With

'If there was no data in the query
'This is where to skip to
No_rstData:

'Cleanup variables that will be reused
Set rstData = Nothing
Set qdf = Nothing
Set XLSheet = Nothing

'Move on to next query
rstSheets.MoveNext

'Start process again with new query and recordset
Loop

'Save the file and open workbook
XLBook.SaveAs "C:\Documents and Settings\" & basUserName.fOSUserName _
& "\Desktop\Cash Processing Internal.xls"
xlApp.Quit

Done:

'Cleanup
Set rstSheets = Nothing
Set db = Nothing
Set XLBook = Nothing
Set xlApp = Nothing
Exit Sub

ErrHandler:

If Err.Number = 3021 Then 'There was no records in data recordset
GoTo No_rstData
Else
MsgBox Err.Description
End If

Resume Done

End Sub

 

[Rob.Mills]

Come to think of it I use Err in all my other code to represent the Error handler and it works. So I'm not sure what's going on with this one.

 

[Shadez]

ok its not the Err,

But from ur code i think i c a bug.

ur error handler is using GoTo No_rstData to return from a handled error.

This turns the error handler OFF!. (Creating the problem you see)

To Keep the error Handler going use Resume No_rstData like you did for DONE.



To turn the error handler fo i use.

on error goto 0

 

[Rob.Mills]

Shadez - You nailed it! Thank you both for your help.

 

[Shadez]