DoCmd.OutputTo destroys DAO.Recordset

[Bill_P]

' Can anyone suggest what to look for to resolve this.

Dim l_Database as DAO.Database
Dim l_Query as DAO.Querydef
Dim lr_Data as DAO.Recordset
Dim ls_HTML_Body as String

On Error GoTo Errs_This_Module_Name

Set l_Database = DBEngine(0) (0)
Set l_Query = l_Database.QueryDefs("qryRptDuesExpireLetter")
l_Query.Parameters(0) = Me.txtDate ' = 30/09/2008
Set lr_Data = l_Query.OpenRecordset

If lr_Data.RecordCount = 0 Then
...
else
Do Until lr_Data.EOF
DoCmd.OutputTo acOutputReport, "rptMemberProfile", acFormatSNP, "C:\NNNNNN_Profile.snp", False
DoEvents
If Not (Send_An_Outlook_Message("Notice of Dues Expiration.", _
lr_Data!FirstName & " " & _
lr_Data!LastName & _
"[mailto:" & lr_Data!Email & "]", _
ls_HTML_Body, _
Email_For("Secretary"), _
Email_For("Treasurer"), _
"C:\NNNNNN_Invoice.doc", _
"C:\NNNNNN_Profile.snp")) Then
....
End if
...
lr_Data.MoveNext
Loop
End if

' WITHOUT the Docmd, all works fine.

' WITH the DoCmd executed but code stepped to the If Not ... statement, the references to lr_Data! Fields are "invalid or the object is no longer set".
' The DoCmd works fine as well.
' 'Yes, other queries occur in the creation of the C:\NNNNNN_Profile.snp file (Report)

 

[Guus2005]

You can't destroy what isn't there

Dim lr_Data as DAO.Recordset
Dim ls_HTML_Body as String

Do Until lr_Data.EOF

You have to open a recordset first before testing for EOF.

 

[Guus2005]

Assuming that you filled the recordset and the recordset isn't empty, you enter the loop.
Not sure what the report is supposed to do, or if there are any queries that where executed during the execution of this line?

 

[RuralGuy]

Do you have your errors turned off? An unexpected error can clear variables.

 

[Bill_P]

Errors off!

I'm sorry, but I do not understand what I would do to do that.

As stated, the program works without error up to and including the DoCmd
When debugging to the Docmd, the lr_Data![objects] exist; Once the DoCmd has executed (I believe without error), the lr_Data![objects] are "invalid or no longer set".

 

[unclejoe]

Hi Bill,

Try removing the "DoEvents" and see if it works.

I suspect there might be more than just your code.

I'm sorry, but I do not understand what I would do to do that.

As stated, the program works without error up to and including the DoCmd
When debugging to the Docmd, the lr_Data![objects] exist; Once the DoCmd has executed (I believe without error), the lr_Data![objects] are "invalid or no longer set".

 

[Bill_P]

DoEvents

Yes, I've tried that.

Yes, there is much more code BUT I need to know WHAT to look for as the error is much deeper, but where?

 

[unclejoe]

Hi Bill,

Just a guess, is your report "rptMemberProfile" and "qryRptDuesExpireLetter" share the same query source or table? (or something similar?)

Sometimes, sharing opened tables or queries can cause the recordset to close.

Yes, I've tried that.
Yes, there is much more code BUT I need to know WHAT to look for as the error is much deeper, but where?

 

[Bill_P]

Response to Uncle_Joe

Yes, there would be a common reference to a Table. Is there a technique to get around such an occurrence?

 

[Bill_P]

Attachment of the two queries

Refer attachment

 

[unclejoe]

Hi Bill,

Not sure about this, but if the Report Record Source is using the same query as in the code, the report will close the query. This might be your problem?

An alternative is to open a recordset using SQL syntax.

Dim l_Database as DAO.Database
Dim l_Query as DAO.Querydef
Dim lr_Data as DAO.Recordset
Dim ls_HTML_Body as String

On Error GoTo Errs_This_Module_Name

Set l_Database = DBEngine(0) (0)
’Remove this…
’Set l_Query = l_Database.QueryDefs("qryRptDuesExpireLetter")
’l_Query.Parameters(0) = Me.txtDate ' = 30/09/2008
‘………………….

‘Change to this…….
Set lr_Data = OpenRecordset(“SELECT “WhateverHere” FROM “WhatEverTable” WHERE SomeDate = #” & Me.txtDate & “#” )

If lr_Data.RecordCount = 0 Then

Yes, there would be a common reference to a Table. Is there a technique to get around such an occurrence?

 

[Bill_P]

Resolved

This difficulty has been resolved.

The Report rptMemberProfile has 7 sub reports, the last of which had a Parameter Query as its record source.

A simple statement by John Viescas (johnv@viescas.com) that DoCmd.OutputTo can NOT include Parameter Queries enabled this to be resolved.

Many Thanks to John, and UncleJoe for their help.

Bill_P

 

[unclejoe]

Hi Bill,

Glad to hear from you. Yes, if you have parameters in your query within a query.

This difficulty has been resolved.
The Report rptMemberProfile has 7 sub reports, the last of which had a Parameter Query as its record source.
A simple statement by John Viescas (johnv@viescas.com) that DoCmd.OutputTo can NOT include Parameter Queries enabled this to be resolved.
Many Thanks to John, and UncleJoe for their help.
Bill_P