469,342 Members | 5,948 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

Emailing Reports with a variable Query Source in VBA


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.
Dec 18 '06 #1
2 1522
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)
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.
Dec 18 '06 #2
14,534 Expert Mod 8TB
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.


Expand|Select|Wrap|Line Numbers
  1. WHERE (((SLUA.SLUA)=" & intSLUA & "))"
Dec 19 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Brendan MAther | last post: by
4 posts views Thread by BernardNem via AccessMonster.com | last post: by
1 post views Thread by Ron | last post: by
8 posts views Thread by marjbell | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.