In what is becoming a series, we’ll further tweak our code to allow for filtering of the query.
In the original code, we open a query directly as a recordset. This fails if the query requires some parameters.
(I’m not going to demonstrate a way to get user input and use that as the parameter. You should be able to copy and paste the code from the original user input sections of the code and modify as needed.)
To start, let’s discuss the query and it’s parameter.
In our original code, the query was just pulling a list of email addresses. For this, let’s filter that list of addresses by domain.
(For our purposes, I’ll assume you know how to write a query with parameters. Here’s a screeenshot of how yours should look.)
You can see that we added a parameter called “domain” and the told Access that we want the query to return any record that has “domain” at the end.
So, when we run the query from the design window, we’re prompted to enter the parameter:
and when we click RUN we’ll get all email addresses from the jephens.com domain.
That’s all well and good, but if we run the code as it exists, we get an error:
This occurs because the code doesn’t know what to do with the parameter, so it punts and throws an error.
We’ll need to handle this. And for that, we have to use a QueryDef object and assign a parameter.
So, instead of:
Set MailList = db.OpenRecordset("MyEmailAddresses")
… we need to do a few things:
1. Create a QueryDef object
2 Assign our query to the object
3. Tell the object what our parameter is
4. Put the output of our query into our recordset.
Happily, this is pretty simple.
Dim qryMail as QueryDef
Set qryMail = currentdb.querydefs("MyEmailAddresses")
qryMail("domain") = ".com"
Set MailList = qryMail.OpenRecordset
And that will filter the recordset and only send mails to people in the .com namespace.
Of course, that’s not the most user-friendly code, so you’d probably want to copy and paste some of the user input language and use that to fill in the parameter:
Dim Domain as string, qryMail as QueryDef
Domain$ = InputBox$("Please enter domain you would like to filter. Leave blank to send to everyone on the list.", "Users can be choosers!")
Set qryMail = currentdb.querydefs("MyEmailAddresses")
qryMail("domain") = domain$
Set MailList = qryMail.OpenRecordset
The nice thing about the way we wrote the query is we can leave the domain part blank, and the query will return all the records.
I cant see how to modify the code for my purpose. In my query I have two columns selected from a table, the email (of the eventual mailto) and the name (real name associated with the email address). The criteria for the query is in the name column and is Like[formname]![fieldname]. How can I code to replicate that?
Even if I just keep it simple and run the EmailAddresses query without any parameters and get its source from my other query, you still get the runtime error. Is there a simple way to not have to amend the code too much but allow the query based on a query to work?
If you’re launching from a form, then you need to pass the form field to the routine.
The trick is NOT referring to the form field in your query, but in the code. The query needs a generic parameter (“domain”) in our example, and then you fill the generic parameter in with something specific.
So, the code would be something like:
Dim qryMail as QueryDef
Set qryMail = currentdb.querydefs(“MyEmailAddresses”)
qryMail(“domain”) =[formname]![fieldname]
Set MailList = qryMail.OpenRecordset
Hi,
iam glad that i found you site which is really helpful to me .btw can you help in this below mentioned issue?
i have set up inventory stuff which has fields like :qty :500,id:1,supplier info:dcm,
and if user A enters information like qty to 100 id:2 supplier info:dcm
then current stock should display me 400 meaning 500-100 and it shoot email as soon as it gets current stock for all the users . iam using ms access 2007 and outlook 2007
Please get back to me on my email id .
looking forward to hear from you
Prasu
Prasu,
I don’t reply to emails directly since the blog is meant to be a learning resource, and I’d prefer everyone be able to share in my response.
The question is how/where is the user entering the “100” which causes a display of “400”?
If it’s in a form, then you will need to kick off an e-mail to the applicable users in the form’s events. If it’s directly in a table, then you’re in for a lot more work since Access doesn’t support triggers (events which happen when data is manipulated directly at the table level) — so you’d have to write something that checked the tables every so often looking for a change and then kick off the email based on that.
I can’t offer much more specific help than that; but it’s a place to start looking.
Please assist me on how to send an automated email to different users and attach a report that has filtered only that user’s information automatically, without sending other peoples information. I’m using MS Access 2007.
You would need to call a query each time through the loop.
So you have the first query which provides all the email addresses, then you’d need to run a second query which uses information (UserID, email address, whatever) from the first quer to pull out whatever information you need.
Unfortunately, this isn’t something I’m able to write up in great detail right now, but I’ll put it on the list for a new article.
Firstly, thanks for your invaluable coding for sending emails from Access 2007. I have managed to output an email to outlook, and add an attachment. However the attachment is a report (using a query with Like[enter] etc, and thus the user will need to input their required contact name twice, once for the email and once for the attachment. Is there anyway that I can populate the second InputBox from the result of the first?
You can pass parameters to a query, so the user only has to be asked once, and then you can pass their answer as many times as you want.
Basically, you create a parameter object and pass it to the query object and then execute the query.
Or you might be able to — instead of executing the query — make that query the recordsource for the report.
Jeff, Really appreciate your swift reply. Since your reply on the 26th, however, I have now struggled to do what you suggest. I must have spent hours on the internet trying to decipher how to pass a parameter from VBA to a query. There are too many geeks out there trying to show off their advanced knowledge, and we beginners have little hope of understanding more than 1% of what they have to say. I don’t expect others to do all the work for me but it would be helpful if you could elaborate on your previous reply. I think I have to go with the create a parameter object (think I’ve done that via my first Inbox response) but what do I put in the query to use this response?
Hazel, I guess the first question I have is “how are you attaching the report to the email” ?
I use code:
DoCmd.RunSavedImportExport (“ReportName”)
This avoids all the prompts coming up when exporting.
Then I import these onto the email with:
MyMail.Attachments.Add “path/Report.rtf”
I have now managed to save the user input in a table, but I am stumped again as to how to use the table data in a query without yet another inputbox coming up.
You can fake it by setting your report recordsource to a query and then re-write the query from the code and then do the export from there. Since we have to rely on DoCmd in Access sometimes, we’re robbed of some flexibility as to what we can pass.
Perhaps there’s an easier way, but this seems most direct:
I have a report, REPORT1 and it has a RecordSource of a query called “REPORTSOURCE”. You have saved a variable called YourVariable somewhere, use it when you rewrite your query:
Dim MyQuery As QueryDef
Set MyQuery = CurrentDb.QueryDefs(“ReportSource”)
MyQuery.SQL = “select * from emails where emailaddress = ‘” & YourVariable & “‘”
MyQuery.Close
And then run your export and attach as usual.
You can get the SQL from the QueryDef from the Query Designer in Access, just go into SQL view and then cut and paste into your code module.
Hi Jeff, Thanks so much for your swift response, however the code fails on the SQL and because it fails before the MyQuery.Close I lose my original query and have to start over. What is wrong with my SQL line? Also I don’t quite understand the references to email and emailaddress (should I be replacing these? Also should I be removing my Like[Enter]&”*” from my original query?
OK, you have a report, and you have a query. (Make a backup of the query just so you have it.)
The code works fine, if you know how to edit SQL. If not, your best bet is to write the query (with whatever input you want) and then copy that SQL to the routine.
Or, copy and paste the SQL here and I’ll have a quick look.