asked on

Creating multiple form controls to filter records in a subform...

For simplicity's sake, in my Access 2010 database form that contains a subform, let's say I have three check box controls (I actually have much more than three) that I'm using to filter records in an embedded subform.

As you can see from my code below, when Me.chkBoxFilter1 is checked, only records for fldFilter1= True are displayed. When unchecked, all records are displayed.

If IsNull(Me.chkBoxFilter1) Then
   Me.subformColorSurrogation.Form.Filter = ""
   Me.subformColorSurrogation.Form.FilterOn = False

Else
  Me.subformColorSurrogation.Form.Filter = "[fldFilter1]=" & Me.chkBoxFilter1
  Me.subformColorSurrogation.Form.FilterOn = True
End If

Open in new window

I'm looking for a way I can add or remove other filters through the use of my other check boxes. I know I could accomplish this through multiple ELSEIF statements, but is there a way, for instance, when clicking M.chkBoxFilter2 to TRUE, to ONLY pull TRUE records for fldFilter2 without unfiltering the restrictions placed by my other checkboxes?

I hope this makes sense. I really appreciate any insight into this.

Dale Fye

First off, I would encourage you to use a combo with options for All, Checked, UnChecked or something like that to filter Yes/No fields.

Then, I would use code similar to:

Dim varFilter as variant

varFilter = NULL
if NZ(me.cboCheckBox1, "All") = "No" Then
    varFilter = "([CheckBox1] = 0)"
else if NZ(me.cboCheckBox1, "All") = "Yes" Then
    varFilter = "([CheckBox1] = -1)"
end if

Open in new window

Then, to expand on that I'd add:

if NZ(me.cboCheckBox2, "All") = "No" Then
    varFilter = (varFilter + " AND ") & "([CheckBox1] = 0)"
else if NZ(me.cboCheckBox1, "All") = "Yes" Then
    varFilter = (varFilter + " AND ") & "([CheckBox1] = -1)"
end if

Open in new window

The part of the code that does:

(varFilter + " AND ")

Open in new window

will return NULL if varFilter is still when that segment of code is encountered.

You can then expand your filter until you are done, and at the bottom of the cmd_Filter_Click event, I use:

If Len(varFilter & "") = 0 then
    me.FilterOn = false
else
    me.Filter = cstr(varFilter)
    me.FilterOn = true
endif

Open in new window

Hope this helps

SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KP_SoCal

ASKER

Mr. Edwards, I have been racking my brain for hours on this, and your solution looks like it will accomplish precisely what I need for my checkbox controls! Thank you!!! =)

However, I tried to incorporate a comboBox control for the third if statement, but I could not get it to work.

If Me.cboItem Then
        strCriteria = strCriteria & " AND [fldItem]=" & Me.cboItem
    End If

Open in new window

From this third if statement, I'm attempting to apply a filter that restricts my displayed records to a specific item within my field "fldItem" in addition to the other filters that I've applied through the first two if statements. Any suggestions on this? Again, I really appreciate your insight on this.

SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KP_SoCal

ASKER

TERRIFIC!!!!!!!! I salute you, and thank so much! =))

Mark Edwards

Thanks. Glad I could help.

I've been doing this since the mid 90's and spend a lot of time training new Access/VBA/SQL Server developers. I try to curtail my solutions to what the author says he needs, unless what he thinks he needs isn't going to work at all. I also try to compile and test all of my solutions before posting them - no half-baked throw-outs that won't even compile.

KP_SoCal

ASKER

Contributors like you are why I am a paid subscriber to this web forum. Thanks again for your help. Have a terrific weekend! =)

Mark Edwards

You too!