Emailing report changing query?

[bigal.nz]

Hi All,

I have a button on a form which emails a report based on the current record. This works, but occasionally (for a reason I cant yet pin-point) the next time you go to email a record the report is empty.

When I look at the underlying query I find that it has been modified. For example:

' *****************************************************
' **** GENERIC EMAIL BUTTON CLICK EVENT ***************
' *****************************************************
Private Sub CmdEmail_Click()
On Error GoTo CmdEmail_Click_Err

Me.Refresh
DoCmd.OpenReport "Daily Report", acViewPreview, , "EventNo='" & Me.EventNo & "'", acHidden
Me.Form.SetFocus
DoCmd.SendObject acSendReport, "Daily Report Event", acFormatPDF, "", "", "", _
"Report " & Me.EventNo
DoCmd.Close acReport, "Daily Report", acSaveNo

' *** ACTIONS LOG UPDATE ***
Actions.Value = Actions.Value & vbNewLine & "Generic Report Email Sent by " & strUser & " on " & Now()

CmdSAMEmail_Click_Err:
    Select Case Err.Number
        Case 2501
        MsgBox ("Email has been cancelled")
        DoCmd.Close acReport, "Daily Report", acSaveNo
        Case 0
        Case 1
        Case Else
        MsgBox ("Error: " & Err.Number & " " & Err.Description)
        DoCmd.Close acReport, "Daily Report", acSaveNo
        Me.Form.SetFocus
    End Select
End Sub

Now the 'Daily Report' is based on a very simple query:

SELECT * FROM SAM;

But sometimes the filter EventNo= is getting left on the end, so it ends up as :

SELECT * FROM SAM WHERE EventNo = '123456789'

Can anyone see what it is in my code that is modifying the underlying query?

Cheers

-Al

 

[Ranman256]

Check the query in the report. If >0 then send it.

If DCount("*","qsRptQuery") >0 then
    Docmd.sendobject....
Else
   MsgBox "No Data"
End if

 

[sneuberg]

The where condition in the DoCmd.OpenReport is "EventNo='" & Me.EventNo & "'" which makes sense if you are only emailing the current record. If that condition weren't specified then the email would send all the records in SAM. I'd check to see if there's a EventNo in SAM that matches the Me.EventNo of the form for those case where the report is empty.

 

[bigal.nz]

Hi Guys,

Thanks for the replies.

The reason as far as I can tell for the report becoming empty is when the query changes from:

SELECT * FROM SAM

becomes

SELECT * FROM SAM WHERE EventNo='123456789'

So the problem is not that I need to test for a empty report, but WHY is the query being permanently modified?

Cheers

-Al

 

[sneuberg]

I suggest you upload your database so we can take a look. I don't see how the code you posted would modify a querydef. Maybe there's some code somewhere else that opens the querydef and modifies it.

 

[bigal.nz]

In that case I think someone else in the database, is doing something that relies on that query which is modifying the querydef.

I will look into it

 

[bigal.nz]

I can not for the life of my find out why the Querydef is changing (but needless to say it is) and the event that triggers it is the emailing of the report.

Is there a way to load a "watch" as it were on the querydef?

Cheers

-Al

 

[sneuberg]

Yes why not. Here's a function that I tested in the attached database

Public Function GetQueryDefSQL(strQueryName)

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs(strQueryName)
GetQueryDefSQL = qdf.SQL
qdf.Close
Set qdf = Nothing

End Function

Put that in a module and then you can call it in you code to see the SQL just add

MsgBox GetQueryDefSQL("[COLOR="Navy"]Query1[/COLOR]")

to see the SQL in a msgbox

or

Debug.Print GetQueryDefSQL([COLOR="navy"]"Query1[/COLOR]")

to see it in the Immediate Window. You will need the change "Query1" to the name of your query.

Please let us know what you find.

 

[sneuberg]

To the extent possible I replicated the code from your original post in the attached database. That process convinces me that the code you posted could not be changing the query. The reason is that that code won't even execute without errors. To get it to run I had to:

• Change CmdSAMEmail_Click_Err: to CmdEmail_Click_Err: To get rid of a "label not found error"
  
• Change "Daily Report Event" to "Daily Report" To get rid of an "Object Not found error" I assume there was only meant to be one report"

I suggest you look elsewhere for the cause of this query being changed. I'd start by just doing a project wise search of the code for the query name.

 

[bigal.nz]

Hey thanks for all your help - I think I found where the QueryDef was getting changed and I have fixed it.

In process of testing now - your right - it was somewhere else.

Regards,

-Al