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.