Use a parameter to return all records if Null

[neuk]

I have a combobox on a continuous form that filters the detail pane based on what the user selects. I created a “select all” option for the combobox with an id of null through a UNION SELECT on the row source.
For the criteria of the id, which is on the forms record source, I wanted the combobox to return all results with a matching id EXCEPT when the “select all” option is selected, in which case I wanted all results,to include null values, to be returned. So, I set the criteria as follows:

Field: ID
Table: tbl1Type
Criteria: [Forms]![frm3PartN]![cboFltrType]
Or: [Forms]![frm3PartN]![cboFltrType] Is Null

After testing this out, it works! However, When I re open the query, Access rewrites the query by removing the “Or” statement and assigning the combobox it’s own field. The newly assigned field looks like this:

Field: [Forms]![frm3PartN]![cboFltrType]


Table: blank
Criteria: blank
Or: Is Null

While I don’t have any problems with it doing that, the problem is that whenever I close the current database and re open it, it asks for the parameter value of [Forms]![frm3PartN]![cboFltrType]. After selecting ok, it next tells me the following

“the expression on Current you entered as the event property setting produced the following error: Object or class does not support the set of events.”

I’m guessing the error is a result of Access rewriting the query by assigning [Forms]![frm3PartN]![cboFltrType] its own field as an “expression”. So, I have to change it back to how I originally had it for it work again. Of course, changing it back every time I reopen the file is not a good solution.

So, is there a way I can disable the query rewrite? Or is theresomething additional I can type into the criteria to circumvent this? Or isthere a better way of going about this? I want to try and keep the solution assimple as possible. I'm hoping one of you gurus may understand what is going on? Thank you for your help.

[June7]

I tested this and I don't get that prompt nor error. Is this query the source for a form set to open by default? Or is it a subform?

If you want to provide db for analysis, follow instructions at bottom of my post.

I don't use dynamic parameterized queries. I use VBA to apply filter to form or report. Review http://allenbrowne.com/ser-62.html

[neuk]

June7,

Thank you for that reference. I redid my control form using VBA and it operates much better. I have used a lot of VBA in Excel but didn’t know where to start in Access. This was a great starting point.

I recommend a small addition to Allen Browne’s script. A small problem you will encounter is that he only tests if the checkbox in the details section is on (-1) or off (0). He says that it isn’t necessary to test for the both condition (1) since “we add nothing to the filter string for other cases (1 or Null).”

 'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.cboFilterIsCorporate = -1 Then
        strWhere = strWhere & "([IsCorporate] = True) AND "
    ElseIf Me.cboFilterIsCorporate = 0 Then
        strWhere = strWhere & "([IsCorporate] = False) AND "
    End If

While this will still work for most cases, we run into a problem if the only thing we want to filter is the “both” condition(1) because no other criteria is entered and therefore, the routine will default to his error message “no criteria”. To avoid this we should still test for the “both” condition (1)

  'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.cboFilterIsCorporate = -1 Then
        strWhere = strWhere & "([IsCorporate] = True) AND "
    ElseIf Me.cboFilterIsCorporate = 0 Then
        strWhere = strWhere & "([IsCorporate] = False) AND "
    ElseIf Me.cboFilterIsCorporate = 1 Then
        strWhere = strWhere & "([IsCorporate] = True Or [IsCorporate] = False) AND "
    End If

By adding the third test condition for(1) we now avoid the error message IF no other criteria is entered.