472,807 Members | 3,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,807 software developers and data experts.

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 1745
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

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

Similar topics

by: Brendan MAther | last post by:
I have a table called Contact_Info. I have a form that allows me to show all the contacts from a specified city and sector. Once these contacts appear on my new form I would like to be able to...
by: RC | last post by:
I can't quite grasp the concept of creating custom reports depending upon what options a user picks on a Form. For example, the user clicks on a "Print Reports" button and a Form pops up. On the...
by: tafs7 | last post by:
My code below is supposed to email me when an error occurs on the application, but it's not emailing anything. Am I missing something? I know the smtp servers I've tried work. I even added a...
by: BernardNem via AccessMonster.com | last post by:
Hi, I am trying to work on an employee database. The tables and fields that I am working on are listed below. I created a query because I wanted to separate the employees by department (selected...
by: JoeW | last post by:
I'm utilizing a database that I created within MS Access within a program I've created in VB.NET. I am using the VB front end to navigate the information, but want to be able to print a report,...
by: Ron | last post by:
Hi All, I needed a report so I used the report wizards to make it based on a query I made (also made with a wizard). That all works great. However, now I need a identical report only based on...
by: crisostomofred | last post by:
I have a report with it's source from a query. The query calls data from 3 related tables. The report is a meeting agenda and I only want to attach a snapshot report to my email based on the...
by: Keriana30 | last post by:
I'm using the following code in an attempt to email reports from a database form in Access. With EMsg Set .Configuration = EConf .To = Recip (variable) .CC = "Susan.Miller@ocfl.net" .From =...
by: marjbell | last post by:
I have a Access database of email addresses that I would like to mass email to customers. Can Access be used through Outlook? or can it just be done with Access? I know it is possible to use...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.