By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,934 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,934 IT Pros & Developers. It's quick & easy.

Emailing Reports with a variable Query Source in VBA

P: 2
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.
Dec 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: 2
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.
Dec 18 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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

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.