About filters

[silverback]

I apologise for being so dense, but can I get some help on filters, please? Try as I might, I can't understand what they are or how they are used and I can't make sense of the Access help.

If I use a DoCmd.OpenForm construct, I see that I can pass a Filter and a Where parameter.
My understanding is that the where parameter acts like the criteria I put on a query in design view; it would seem that it acts dynamically i.e. if I have a form based on a query which returns all records, passing the where parameter to the Openform command restricts the records returned according to the contents e.g. [LastName] = "Smith". Is that understanding correct?
If it is correct, what's the filter for? And what does it do? And how is it different from Where?

Help, please (in words of one syllable, preferably)
Thanks
Silverback

[HansV]

I think the FilterName argument is used far less than the WhereCondition argument. I never use FilterName myself, only WhereCondition.

You could use the FilterName argument if you already have a query in your database that returns the records that you want to display in the form or report. By passing the name of the query in the FilterName argument of DoCmd.OpenForm or DoCmd.OpenReport, the form or report will show only the records that are returned by the query.

I find WhereCondition much more flexible, because you can assemble the string on the fly in the code.

[silverback]

Referring to one of my specific questions, if I have a control on a form based on a query which has no criteria and so, returns all records from a table, if DoCmd.Openform is used with a WhereCondition parameter - say [LastName] = "Smith" - then is it correct that the result shown on the form will be a subset of all the records i.e. only those with the LastName of Smith?

Thanks
Silverback

[HansV]

Yes, that is correct, except that you have to specify the where-condition as a quoted string, so you have to do something about the quotes around the name Smith. There are several ways to do so:

1) Double the quotes around the name:

DoCmd.OpenForm FormName:="frmMyForm", WhereCondition:="[LastName]=""Smith"""

2) Use single quotes around the name:

DoCmd.OpenForm FormName:="frmMyForm", WhereCondition:="[LastName]='Smith'"

3) Concatenate with Chr(34):

DoCmd.OpenForm FormName:="frmMyForm", WhereCondition:="[LastName]=" & Chr(34) & "Smith" & Chr(34)

[silverback]

OK - I understand your clear reply, but I still can't get filtering to work in the following situation.
The users of a volunteers' DB have a new requirement which is to email a number of volunteers at the same time. I produced a form for the volunteers' DB which lists everyone with an email address, allows non-contiguous selections and then invokes an email with the names in the bcc field.
The DB, however, uses Steve Nyberg's extremely sophisticated report filtering suite (Rfil5 – unfortunately the webpage www.stevenyberg.com seems to be no longer available but the package is offered widely on the internet) and the users want to retain the flexibility of this custom filtering when the drop down list on the form is created. I've managed to introduce the form into the suite, but it won't filter the displayed records.
The supplied DB opens on the Custom Filtering form.

• Choose Volunteers by Duty in the top left 'Report' field and then click 'Preview Filtered Report'. The report shows Volunteers by Duty.
  
  Close the report and on the Custom Filtering Form, in the first 'Select Fields' field, choose 'Duty Name', set Expressions to '=' and set 'Values' to 'Clerical'. Preview the report, and it is filtered to show details of only the chosen duty.
  
  Now select 'MultiMail' in the top left Report field. (Ignore warnings to save custom filter.) Click on the 'MultiMail' command button at top right and a form containing a list of 12 email addresses is produced.
  
  Close the form and select DutyName = Clerical on the custom form, click the MultiMail button and the list is not filtered. I can't find out why.

Notes
1. When a filter has been chosen, the 'Where' string the Rfil suite will produce can be seen by clicking the 'Refresh SQL' button on the custom filtering form.
2. The MultiMail list box uses query MultiMailAddresses as its row source and this query is based on MMA1. This is because it has to do a Select Distinct (volunteers can have more than one duty) and I don't think this can be done/don't know how to do this in one query.
3. The main VB module is z_Rfil5_basMain, and the report/form calling routine is rf5CRun

What am I doing wrong, please?
Thanks
Silverback

[HansV]

It's a bit hard to know without seeing the database...

[HansV]

The MultiMail form is unbound, so filtering it has no effect.

The e-mail addresses are displayed in an unbound list box. Neither its row source MultiMailAddresses nor MMA1 is filtered.

It might be possible to apply filtering to one of these queries, but I have no idea if that would break other features of the database.

[silverback]

Well this proves I don't understand filters. Can you expand on why unbound items can't be filtered, please? If I put the duty name box on the form and use a criterion in the MMA1 query (DutyName = [Form]![MultiMail]![DutyName]), it filters the records OK but the list is still unbounded.
Thanks
Silverback

[HansV]

The following is an extremely "dirty" hack because it violates the generic nature of the filter form, but I don't have time to delve deeply into the code, and you edited this part already, I think.

In the rf5CRun function in z_RFil5_basMain:

Code: Select all

    Case 5 ' Calling a form
        If strWhere <> "" Then
            CurrentDb.QueryDefs("MMA1").SQL = _
                "SELECT Volunteers.VolunteerID, Mid([Email],InStr([Email],':')+1," & _
                "InStr(InStr([Email],':')+1,[Email],'#')-InStr([Email],':')-1) AS ShortEmail, " & _
                "Volunteers.LastName, Volunteers.FirstName, Volunteers.Email, " & _
                "VolunteerDuty.DutyTypeID, DutyType.DutyName FROM (Volunteers INNER JOIN " & _
                "VolunteerDuty ON Volunteers.VolunteerID = VolunteerDuty.VolunteerID) " & _
                "INNER JOIN DutyType ON VolunteerDuty.DutyTypeID = DutyType.DutyTypeID " & _
                "WHERE Volunteers.Email Is Not Null AND " & strWhere
        Else
            CurrentDb.QueryDefs("MMA1").SQL = _
                "SELECT Volunteers.VolunteerID, Mid([Email],InStr([Email],':')+1," & _
                "InStr(InStr([Email],':')+1,[Email],'#')-InStr([Email],':')-1) AS ShortEmail, " & _
                "Volunteers.LastName, Volunteers.FirstName, Volunteers.Email, " & _
                "VolunteerDuty.DutyTypeID, DutyType.DutyName FROM (Volunteers INNER JOIN " & _
                "VolunteerDuty ON Volunteers.VolunteerID = VolunteerDuty.VolunteerID) " & _
                "INNER JOIN DutyType ON VolunteerDuty.DutyTypeID = DutyType.DutyTypeID " & _
                "WHERE Volunteers.Email Is Not Null"
        End If
        DoCmd.OpenForm FormName:="MultiMail"
    Case Else
        ...

[silverback]

That's just wonderful.

Thank you very much
Silverback