VBA Function Exits Unexpectedly

[Murph492]

Hello,

I am trying to sort out the following issue. It worked fine on a Windows
2000 machine with Office 2000 installed, but after moving it to an XP machine
with Office 2003, the function dies with no discernable error cause.

I have a function that selects records from a table into a recordset, opens
a report filtered on a field in the record set, then exports the report to
the server in .rtf format.

The loop runs through the first few records fine, but after say 5-8 records,
the function just "dies" (for lack of a better term). It quits running at all
and won't get caught in the error routine.

Here's a synopsis of the code:
Function SendReports()
Create New Record Set RS
Fill RS
For each record in RS
open report "MyReport" filtered on RS("agencynum")
Export MyReport to .rtf
Move to next record
End function

Obviously this is very simplified, so please ask me if you need more clarity.

Does anyone have any suggestions on what to try? I've been frustrated by
this for a while now.

Thanks in advance for any and all help.

 

[Ofer]

In the error capture, do you give a mesage box, or you getting out of the
function?
Do you have any where in the code - "On error resume next"
In the error capture do you have any condition on any error number?
Can you post the function?

 

[David C. Holley]

The only reason that I can think of that would cause a function to 'die'
with no error is that there's some error handling in place that's
causing the function to exit.
First and foremost, go into the code and look for ON ERROR statements
and comment them out.
Then call the function. When you call it should crap out somewhere in
the code and provide you with the actual error that's happening.
David H

 

[Murph492]

Ofer and David:

Thank you for the assistance. Here is the code:
Function RunReports()

On Error GoTo Err_ErrorLog:

Dim month As String, Year As String
month = GetDateValue("month") 'Just a function to get the reporting parameters
Year = GetDateValue("year")

'Create recordset
Dim rstagents As Recordset
Set rstagents = New Recordset
rstagents.CursorLocation = adUseClient

rstagents.Open "Select * From Mytable where (r_LossDetail = 'y')", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'rstagents.Open "Select * From Mytable where r_LossDetail = 'Y' and
[agency number] = '7401'", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do Until loopcount = rstagents.RecordCount
rptFilter = "[Agency] ='" & rstagents("agency number") & "'"
DoCmd.OpenReport "MyReport", acViewPreview, xx, rptFilter, acHidden

rptName = "C:\Reports\" & month & Year & "\LossByAgent\LossDetail-" & _
month & "-" & Year & "-" & rstagents("agency Number") & ".rtf"

DoCmd.OutputTo acOutputReport, "MyReport", acFormatRTF, rptName, False
DoCmd.Close acReport, "Loss Detail by Policy Year2", acSaveNo
rstagents.MoveNext
loopcount = loopcount + 1
Loop
Err_ErrorLog:
Call ErrorLog(Err.Description, Err.Number)
End Function


The thing is, it happens either way, whether or not the error handling code
is in there or not. I've taken it out, left it in, any combination I can
think of. I'm not sure if I mentioned it or not originally, but this code
runs fine on Access 2000, but NOT on 2003. That's where I think the problem
is, but I have no idea what the problem is.
Its also odd that it runs fine for a few of the reports and then just dies
in the middle of running them. Seems like it's almost some sort of memory
issue or something very obscure.

 

[Justin Hoffman]

Ofer and David:

Thank you for the assistance. Here is the code:
Function RunReports()

On Error GoTo Err_ErrorLog:

Dim month As String, Year As String
month = GetDateValue("month") 'Just a function to get the reporting
parameters
Year = GetDateValue("year")

'Create recordset
Dim rstagents As Recordset
Set rstagents = New Recordset
rstagents.CursorLocation = adUseClient

rstagents.Open "Select * From Mytable where (r_LossDetail = 'y')", _
CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

'rstagents.Open "Select * From Mytable where r_LossDetail = 'Y' and
[agency number] = '7401'", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do Until loopcount = rstagents.RecordCount
rptFilter = "[Agency] ='" & rstagents("agency number") & "'"
DoCmd.OpenReport "MyReport", acViewPreview, xx, rptFilter, acHidden

rptName = "C:\Reports\" & month & Year & "\LossByAgent\LossDetail-" & _
month & "-" & Year & "-" & rstagents("agency Number") &
".rtf"

DoCmd.OutputTo acOutputReport, "MyReport", acFormatRTF, rptName, False
DoCmd.Close acReport, "Loss Detail by Policy Year2", acSaveNo
rstagents.MoveNext
loopcount = loopcount + 1
Loop
Err_ErrorLog:
Call ErrorLog(Err.Description, Err.Number)
End Function


The thing is, it happens either way, whether or not the error handling
code
is in there or not. I've taken it out, left it in, any combination I can
think of. I'm not sure if I mentioned it or not originally, but this code
runs fine on Access 2000, but NOT on 2003. That's where I think the
problem
is, but I have no idea what the problem is.
Its also odd that it runs fine for a few of the reports and then just dies
in the middle of running them. Seems like it's almost some sort of memory
issue or something very obscure.

A number of things:

Do you not use Option Explicit to make sure you dimension variables? You
seem not to and this is almost always a pretty bad start.

I would give variables prefixes such as strMonth and strYear since Month and
Year are vba functions this could cause confusion.

Structure error handling so routine is only called if there is an error.
Yours will always run (although your routine may handle the case when
Err.Number is 0, this seems a waste of time)

Why do you open a recordset specifying CursorLocation, CursorType and
LockType when what you have specified is needlessly slower than the default?
Surely you just need a forward-only read-only recordset which you can loop
through to get the values.

I would use While Not rst.EOF ... Wend to loop through the records.

Although others might not bother, I would close the recordset and set it to
nothing.

There are other pretty important things like checking the folder exists
before saving a file there and why do you open "MyReport" but close Loss
Detail by Policy Year2". Anyway, if it is of any help, I would structure my
code more like shown below. Although an even better way to structure would
be to create a function SaveAsRTF(strAgency As String, lngMonth As Long,
lngYear As Long) As Boolean so that you could get a yes/no answer as to
whether each file had been created. You could then call this function from
inside of the recordset looping.


Function RunReports()

On Error GoTo Err_Handler

Dim strMonth As String
Dim strYear As String
Dim strSQL As String
Dim strWhere As String
Dim strPath As String
Dim rstAgents As ADODB.Recordset

strMonth = GetDateValue("month")
strYear = GetDateValue("year")
strSQL = "Select * From Mytable where (r_LossDetail = 'y')"

Set rstAgents = New ADODB.Recordset

rstAgents.Open strSQL, CurrentProject.Connection

While Not rstAgents.EOF
strWhere = "blah,blah,blah"
strPath = "blah,blah,blah"

' Rest of code here

rstAgents.MoveNext
Wend

Exit_Handler:

On Error Resume Next
rstAgents.Close
Set rstAgents = Nothing

Exit Function

Err_Handler:
Call ErrorLog(Err.Description, Err.Number)
Resume Exit_Handler

End Sub

 

[Murph492]

Justin,

I appreciate your input and will look into it further. This is more of
something I inherited and am trying to sort through: I didn't write the
function, and my knowledge of VBA/Access Programming is limited.

"There are other pretty important things like checking the folder exists
before saving a file there and why do you open "MyReport" but close Loss
Detail by Policy Year2" --> The folder always exists and the MyReport and
Loss Detail...are the same report, I just was trying to give it a generic
name and didn't replace the second instance.

Thanks again for your input. Hopefully this may correct the issue.

Ofer and David:

Thank you for the assistance. Here is the code:
Function RunReports()

On Error GoTo Err_ErrorLog:

Dim month As String, Year As String
month = GetDateValue("month") 'Just a function to get the reporting
parameters
Year = GetDateValue("year")

'Create recordset
Dim rstagents As Recordset
Set rstagents = New Recordset
rstagents.CursorLocation = adUseClient

rstagents.Open "Select * From Mytable where (r_LossDetail = 'y')", _
CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

'rstagents.Open "Select * From Mytable where r_LossDetail = 'Y' and
[agency number] = '7401'", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Do Until loopcount = rstagents.RecordCount
rptFilter = "[Agency] ='" & rstagents("agency number") & "'"
DoCmd.OpenReport "MyReport", acViewPreview, xx, rptFilter, acHidden

rptName = "C:\Reports\" & month & Year & "\LossByAgent\LossDetail-" & _
month & "-" & Year & "-" & rstagents("agency Number") &
".rtf"

DoCmd.OutputTo acOutputReport, "MyReport", acFormatRTF, rptName, False
DoCmd.Close acReport, "Loss Detail by Policy Year2", acSaveNo
rstagents.MoveNext
loopcount = loopcount + 1
Loop
Err_ErrorLog:
Call ErrorLog(Err.Description, Err.Number)
End Function


The thing is, it happens either way, whether or not the error handling
code
is in there or not. I've taken it out, left it in, any combination I can
think of. I'm not sure if I mentioned it or not originally, but this code
runs fine on Access 2000, but NOT on 2003. That's where I think the
problem
is, but I have no idea what the problem is.
Its also odd that it runs fine for a few of the reports and then just dies
in the middle of running them. Seems like it's almost some sort of memory
issue or something very obscure.

A number of things:

Do you not use Option Explicit to make sure you dimension variables? You
seem not to and this is almost always a pretty bad start.

I would give variables prefixes such as strMonth and strYear since Month and
Year are vba functions this could cause confusion.

Structure error handling so routine is only called if there is an error.
Yours will always run (although your routine may handle the case when
Err.Number is 0, this seems a waste of time)

Why do you open a recordset specifying CursorLocation, CursorType and
LockType when what you have specified is needlessly slower than the default?
Surely you just need a forward-only read-only recordset which you can loop
through to get the values.

I would use While Not rst.EOF ... Wend to loop through the records.

Although others might not bother, I would close the recordset and set it to
nothing.

There are other pretty important things like checking the folder exists
before saving a file there and why do you open "MyReport" but close Loss
Detail by Policy Year2". Anyway, if it is of any help, I would structure my
code more like shown below. Although an even better way to structure would
be to create a function SaveAsRTF(strAgency As String, lngMonth As Long,
lngYear As Long) As Boolean so that you could get a yes/no answer as to
whether each file had been created. You could then call this function from
inside of the recordset looping.


Function RunReports()

On Error GoTo Err_Handler

Dim strMonth As String
Dim strYear As String
Dim strSQL As String
Dim strWhere As String
Dim strPath As String
Dim rstAgents As ADODB.Recordset

strMonth = GetDateValue("month")
strYear = GetDateValue("year")
strSQL = "Select * From Mytable where (r_LossDetail = 'y')"

Set rstAgents = New ADODB.Recordset

rstAgents.Open strSQL, CurrentProject.Connection

While Not rstAgents.EOF
strWhere = "blah,blah,blah"
strPath = "blah,blah,blah"

' Rest of code here

rstAgents.MoveNext
Wend

Exit_Handler:

On Error Resume Next
rstAgents.Close
Set rstAgents = Nothing

Exit Function

Err_Handler:
Call ErrorLog(Err.Description, Err.Number)
Resume Exit_Handler

End Sub

 

[Murph492]

Justin,

I revamped the code and I'm still having the same issue however. The code
was restructured to match yours exactly and it still just craps out in the
middle of the loop.

Something is really funky, I just can't figure out what the heck it is.

Thanks again.

 

[David C. Holley]

When you stay it craps out, are you getting the crap out message giving
you the option to END or DEBUG? or is it that the code just seems to
end? As a next step, I would put a STOP statement at the beginning of
the procedure and then execute whatever event calls the code. The STOP
statement will cause the VBA editor window to display once it does the
F8 key can be used to step through the code line by line. This will
hopefully help in finding the problem. You may need to display the
LOCALS WINDOW to view the values of the variables used in the code.

 

[Murph492]

David,

No, I don't get the "End or debug" message at all. The code just stops
running. I will try the STOP statement next and check the results. Thanks for
the assistance.

 

[Murph492]

Just an FYI and thank you to all of those who offered assistance. Apparently
this problem was solved by installing the HotFix as referenced in KB Article
898072. Odd, but it seems to have solved it without any problem. I ran the
hotfix installer and ran the function immediately after installing it, and it
was fine.

Very odd.

Thanks again everyone.