Multiple filters in a form (1 Viewer)

T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
All,
Thanks in advance for the help.

I have a form with 2 combo boxes, both able to filter a given field.
They both work fine in isolation, but I cannot make then filter together. When I run one filter the other turns off.

Here is the code for each Combo Box


Private Sub Combo317_AfterUpdate()
Select Case Me.Combo317
Case "Active"
Me.Filter = "Invbillrecon = -1" 'show only Active
'ReconInvBill
Me.FilterOn = True
Case "Inactive"
Me.Filter = "Invbillrecon = 0" 'show only inactive
Me.FilterOn = True
Case "all"
Me.FilterOn = False 'show everything
End Select

End Sub

Note: Default value is "Active"



Private Sub Combo319_AfterUpdate()
Select Case Me.Combo319
Case "Out Of Balance"
Me.Filter = "UnitsDiff <> 0" 'show only unreconciled
'ReconInvBill
Me.FilterOn = True
Case "In Balance"
Me.Filter = "UnitsDiff = 0" 'show only reconciled
Me.FilterOn = True
Case "all"
Me.FilterOn = False 'show everything
End Select

End Sub

Note: Default value is "<>0"


Really appreciate any help
For example, when the form opens it should filter to only the Invbillrecon field to Active records AND the UnitsDiff field to <> 0.
 
moke123

moke123

AWF VIP
Local time
Today, 08:15
Joined
Jan 11, 2013
Messages
3,913
You need to build the filter string along the lines of ...

Code: 
Private Sub FilterMe()

    Dim strFilter As strFilter

    If Me.Combo317 = "Active" Then

        strFilter = strFilter & "Invbillrecon = -1 And "

    ElseIf Me.Combo317 = "Inactive" Then

        strFilter = strFilter & "Invbillrecon = 0 And "

    ElseIf Me.Combo317 = "All" Then

        strFilter = strFilter & ""

    End If



    If Me.Combo319 = "Out Of Balance" Then

        strFilter = "UnitsDiff <> 0 And "

    ElseIf Me.Combo319 = "In Balance" Then

        strFilter = strFilter & "UnitsDiff = 0 And "

    ElseIf Me.Combo319 = "all" Then

        strFilter = strFilter & "" 

    End If

    If strFilter <> "" Then

        strFilter = Left(strFilter, Len(strFilter) - 4)

    End If

    Me.Filter = strFilter

    Me.FilterOn = True

End Sub

then you would call the procedure in the after update of the combos.
 
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
I cant get this to work.
Lets try a slightly different approach. Maybe it is the same code with a small tweak.
Lets set the combo boxes to their values then apply the filters with a button ("Command329").

On Click the Form filters based on the values in BOTH combo boxes.

Combo Box Values

Combo317: Possible values
If Combo Box Value = "Active" Field InvBillRecon shows only values =-1
If Combo Box Value = "Inactive" Field InvBillRecon shows only values =0
If Combo Box Value = "All" Field InvBillRecon shows All values

Combo319: Possible Values
If Combo Box Value = "OutOfBalance" Field UnitsDiff shows only values <> 0
If Combo Box Value = "InBalance" Field UnitsDiff shows only values = 0
If Combo Box Value = "All" Field UnitsDiff shows All values

___________
All the combinations should be below (code obviously not working):

Private Sub Command329_Click()
If Me.Combo317 = "Active" And Me.Combo319 = "Out of balance" Then

InvBillRecon = -1 And UnitsDiff <> 0

ElseIf Me.Combo317 = "Active" And Me.Combo319 = "In Balance" Then

InvBillRecon = -1 And UnitsDiff = 0

ElseIf Me.Combo317 = "Active" And Me.Combo319 = "All" Then

InvBillRecon = -1


ElseIf Me.Combo317 = "InActive" And Me.Combo319 = "Out of balance" Then

InvBillRecon = 0 And UnitsDiff <> 0

ElseIf Me.Combo317 = "InActive" And Me.Combo319 = "In balance" Then

InvBillRecon = 0 And UnitsDiff = 0

ElseIf Me.Combo317 = "InActive" And Me.Combo319 = "All" Then

InvBillRecon = 0

If Me.Combo317 = "All" And Me.Combo319 = "Out of balance" Then

UnitsDiff <> 0

ElseIf Me.Combo317 = "All" And Me.Combo319 = "In Balance" Then

UnitsDiff = 1

End if
End Sub
 
moke123

moke123

AWF VIP
Local time
Today, 08:15
Joined
Jan 11, 2013
Messages
3,913
For example, when the form opens it should filter to only the Invbillrecon field to Active records AND the UnitsDiff field to <> 0.
Click to expand...
First question, do you have the combos default values set to these?

I usually build the filter in its own procedure. then with a button or control event you can call the filter procedure. so in the example I posted you would have
Code: 
Private Sub Command329_Click()
FilterMe
end sub
which then builds the filter and applies it.

try adding a debug.print strFilter to the end of the code so you can see what the filter string ends up looking like. You may need to tweak it or post your db and i'll look.
 
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
Yes, default values are set to
Combo317 = "Active"

Combo319 = "OutOfBalance"

Let me try your suggestion and see what happens. Thanks
 
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
The Code was added to the On Click event for the button.

Compile Error:
User-Defined Type Not Defined

And it highlights the
Dim strFilter As strFilter

Unfortunately I cannot paste the DB.

Full code now is

Code: 
Private Sub Command329_Click()
    Dim strFilter As strFilter
    If Me.Combo317 = "Active" Then
        strFilter = strFilter & "Invbillrecon = -1 And "
    ElseIf Me.Combo317 = "Inactive" Then
        strFilter = strFilter & "Invbillrecon = 0 And "
    ElseIf Me.Combo317 = "All" Then
        strFilter = strFilter & ""
    End If
 
    If Me.Combo319 = "Out Of Balance" Then
        strFilter = "UnitsDiff <> 0 And "
    ElseIf Me.Combo319 = "In Balance" Then
        strFilter = strFilter & "UnitsDiff = 0 And "
    ElseIf Me.Combo319 = "all" Then
        strFilter = strFilter & ""
    End If
    If strFilter <> "" Then
        strFilter = Left(strFilter, Len(strFilter) - 4)
    End If
    Me.Filter = strFilter
    Me.FilterOn = True
End Sub
 
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
Still not working - so lets simplify. Sorry to be a bother.
Lets let the just 1 combo box (instead of multiple) to hold the filter values and trigger (instead of the button) the filter.

Combo317 has two value options, "to Review", and "All"

To Review: will filter field Invbillrecon to only show values -1 AND field UnitsDiff to only show values <> 0

All: will show every InvBillrecon value (which will automatically capture all UnitsDiff Values)

Here is the code. I'm getting an error on the

Me.Filter = "Invbillrecon = -1 and " & "UnitsDiff <> 0 and " 'show only Active

line. Tried a few things with no luck.

As always thanks for the help.


Code: 
Private Sub Combo317_Click()
    Select Case Me.Combo317
    Case "To Review"
        Me.Filter = "Invbillrecon = -1 and " & "UnitsDiff <> 0 and " 'show only Active
        Me.FilterOn = True
    Case "All"
        Me.FilterOn = False   'show everything
    End Select
End Sub
 
Last edited:
moke123

moke123

AWF VIP
Local time
Today, 08:15
Joined
Jan 11, 2013
Messages
3,913
Maybe an example will make it easier.

It looks like your using yes/no fields (0,-1) so i made a quick table with yes/no fields.

when you select from the combos it constructs a concatenated string (strFilter) which will look like Invbillrecon = -1 And UnitsDiff = 0 And

you then strip off the trailing And to get your strFilter and apply it.

look at this example
 

Attachments

  • filterEX.accdb
    436 KB · Views: 56
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
Moke - thanks. I see you put a lot of thought into this and I really appreciate it.

The Invbillreocn is yes/no, the unitsdiff is a value

I can do an example but before that I have to ask - it seems like the last code I have above gets me pretty close to where I want to go, but has a syntax error on that one line.
Base don your notes - Is it because of the "and" that Access adds? Is there a way to fix just that one line or is the code fundamentally flawed?
 
Last edited:
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
Here is the sample.

The default "to review" Should only show the Joan record

The All setting should show all records
 

Attachments

  • Database181.accdb
    404 KB · Views: 50
moke123

moke123

AWF VIP
Local time
Today, 08:15
Joined
Jan 11, 2013
Messages
3,913
Is it because of the "and" that Access adds?
Click to expand...
Access doesn't add it, your code does. If you are only applying one filter you dont need it.

The purpose of concatenating the filter string is to apply multiple filters. So you add an "And" to the end of each part. After getting the whole string you remove the last "And" and you have your filter string. I personally like to put the code in its own procedure ( Private Sub FilterMe) and then call it from an event.
 
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
OK - got it. Took that out and still getting a syntax error. Please see the attached DB in last post. Thanks again.
 
moke123

moke123

AWF VIP
Local time
Today, 08:15
Joined
Jan 11, 2013
Messages
3,913
in your code this line
Code: 
Me.Filter = "Invbillrecon = -1 " & "UnitsDiff <> 0"  'show only Active

should be
Code: 
Me.Filter = "Invbillrecon = -1  and UnitsDiff <> 0"
 
T

Tskutnik

Registered User.
Local time
Today, 08:15
Joined
Sep 15, 2012
Messages
229
Finally!!!! Thanks very much Moke and everyone. Have a great weekend
 
You must log in or register to reply here.

Similar threads

C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
267
CraigDouglas
C
J
Solved Conflict in code for two controls
Replies
15
Views
731
Pat Hartman
P
Superpat
Solved Synchronize 2 Combox
Replies
12
Views
738
Superpat
Superpat
N
Solved How to filter a single form in MS Access with VBA
Replies
12
Views
471
nector
N
MajP
Show / Hide Columns Easily in Datasheet
Replies
3
Views
277
skymou
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom