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.)

Parameter Query

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:

Entered 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:

Too Few Parameters

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.