Emailing Reports with a variable Query Source in VBA

[modfreq]

Hi,

I am fairly new to Access and VBA. I have found a few threads relating to my problem, but none have led to a solution, so if I hope that I am not restating an old question...

I am writing VBA code that emails users when their license is expiring. I want the email to include a report with a summary of the data we have on file for them as an attachment.

I have a query which is the RecordSource for the User Summary Report. I have a condition on the UserID field in the query that requests a UserID input which filters the report.


I was attempting to create a query in VBA with the condition UserID = intUserID a variable Ive stored per user.

What I'm stuck on, is this... Is it possible to have a saved query say "SummaryQuery" in my database to which I can, in VBA, apply the UserID variable filter temporarily and email a Report based on that temp query? Then reset it and send a report based on the next temp query, etc? Even a pointer as to what expressions I would need to do this would be great help.

Or is there another easier way that I'm not seeing?

Thanks for your help.

[modfreq]

Well, I may have already solved this. I grabbed some code from the web to pull it together. But Im getting 'Run-time error '3464': Data type mismatch in criteria expression' and I'm not sure what is wrong. Here is the code:

Dim qdfNew As DAO.QueryDef
Dim strSQL As String
Dim intSLUA As Integer


strSQL = "SELECT SLUA.SLUA, SLUA.Name, SLUA.StartDate, SLUA.ExpirationDate, SLUA.FirstNotificationSent, LabContacts.LabContacts FROM (SLUA INNER JOIN PrincipleInvestigators ON SLUA.SLUA = PrincipleInvestigators.[SLUA #]) INNER JOIN LabContacts ON PrincipleInvestigators.[SLUA #] = LabContacts.[SLUA #] WHERE (((SLUA.SLUA)='" & intSLUA & "'))"


With CurrentDb

.QueryDefs.Delete ("My_Query")

Set qdfNew = .CreateQueryDef("My_Query", strSQL)
.Close
End With

strReportPath = "C:\SLUA_" & intSLUA & "_Summary.rtf"

DoCmd.OutputTo acOutputReport, "LabContacts", acFormatRTF, strReportPath

The report is named "LabContacts" and it is based on the query "My_Query"
I then attach the SLUA_###_Summary.rtf file to the email using an outlook connection. This works for me since I need to have a copy of the summary saved anyway.

Any idea why I'm getting this error? I'm using Access 2003.

[MMcCarthy]

I assume because you called it intSLUA that it's a number. In which case you don't need the single quotes which are for text only.

Mary

WHERE (((SLUA.SLUA)=" & intSLUA & "))"