Filter Subform via Two Combo Boxes in MainForm

[tammyl]

Hi,

I'm trying to filter my subform records via two combo boxes from the main form. Catch is, i want the option to leave either of the combo selections blank (null).

That is;
- list all records that match Project Code and ALL vendors, if cboVendor is null
OR
- list all records that match Vendor if cboProjectCode is null.

STRUCTURE

tblProject - ProjectCode
tblInvoices - ProjectCode

MainForm = fInv
ComboBox 1 = cboProjectCode
combobox 2 = cboVendor

Subform = fsubInv

Relationship link is the ProjectCode field.

I'm currently using the Link Master & Link Child fields under the subform, as follows.

Link Master Fields = ProjectCode;cboProjectCode;cboVendor
Link Child Fields = ProjectCode;ProjectCode;Vendor

For both combo's i have in the AfterUpdate event
Me.fsubInv.Requery

1) Current setup not working
2) Not sure this is the best way to do this filter for subform???
3) Current setup doesn't allow either field to remain blank
4) it doesn't show any of the invoice information, only the vendor name is displayed in the subform.
5) requery in my Afterupdate doesn't appear to be working.

Without having a 'Run Filter' button (not my preference, as it's another keystroke for the user)
I may need to run the requery twice, first when combo (cboProjectCode) is selected, then again with the selection from combo (cboVendor)

Once i get the basics working, i also wish to have a tick box to only show active records (where paid date is blank)

Hopefully this is relatively straight forward for the Access guru's and someone is happy to help.

Any advise or help appreciated.

Thanks
tammyl

[OBP]

Tammyl, have a look at the "Search2000" database that I posted here
http://www.vbaexpress.com/forum/showthread.php?t=25027
on post #8.
It uses VBA to build a Filter for the Form, including using Combos and might well be what you are looking for, although the user does have to click a Filter button and a Reset buton.
Or you could have a look at the database I posted in post #7 of that thread that uses Query Criteria to do the filtering. You could use the Query to provide the Control Source for your Subform.

[CreganTur]

If neither of Tony's suggestions works for you, then you will have to use an If conditional to evaluate which comboboxes have selected values and then run the appropriate code to pull only from the combobox with a non-null value.

Basically:
[vba]If Not (IsNull(Me.Combobox1) AND IsNull(Me.Combobox2)) Then
'do stuff
Else
'do other stuff
End If[/vba]

The above is very rough pseudo-code, but it gives the basic idea.

[tammyl]

Thanks guys, I shall have a go and let you know.

Cheers
tammyl

[tammyl]

Hi guys,

Thanks for the info. i used it to put a afterupdate event for each of my combo's to try to eliminate the need for the filter button.

I have the following code now attached to my combo boxes. Still got a few problems.
1) When i select criteria from ProjectCode combo, a dialog screen pops up
> Enter Parameter Value - Vendor code
Vendor code is my second combo box
2) Code stops at Me.FilterOn = True is the cboProjectCode_AfterUpdate() macro.

Option Compare Database
Option Explicit
Dim strF As String
 
Private Sub cboProjectCode_AfterUpdate()
    'On Error Resume Next
    BuildFilter
    
    ' If criteria selected to Filter on
    If strF <> "" Or IsNull(strF) Then
        ' Assign the filter string to build form filter
        Me.Filter = strF
        ' Turn on form filter
        Me.FilterOn = True
    Else
        ' Turn off form filter
        Me.FilterOn = False
    End If
       
End Sub
Private Sub cboVendor_AfterUpdate()
    'On Error Resume Next
    BuildFilter
    
    If strF <> "" Or IsNull(strF) Then
        Me.Filter = strF
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
    
End Sub
Private Sub BuildFilter()
    ' Make sure at least one criteria is selected.
    If (IsNull(cboProjectCode) Or cboProjectCode = "") And (IsNull(cboVendor) Or cboVendor = "") Then
        strF = ""
    Else
        If IsNull(cboProjectCode) = False And cboProjectCode <> "" Then
            strF = "[ProjectCode]='" & cboProjectCode & "'"
        Else
            strF = "[ProjectCode] LIKE '*' "
        End If
        
        If IsNull(cboVendor) = False And cboVendor <> "" Then
            strF = "[Vendor]='" & cboVendor & "'"
        Else
            strF = "[Vendor] LIKE '*' "
        End If
    End If
    
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.cboProjectCode = Null
    Me.cboVendor = Null
End Sub

Hope you can help.

Thanks tammyl

[CreganTur]

I've made a few changes to your code that I hope will fix your problem.

First, you have this: "If strF <> "" Or IsNull(strF) Then "
I imagine that what you want is "if strF is not empty string or if strF is not null" - so I changed it to: "If strF <> "" Or Not IsNull(strF) Then". By making this change, I've ensured that the If will return False even when strF is a null value.

I also added the Me. keyword in a couple of places where you left it off.

[vba]Option Compare Database
Option Explicit
Dim strF As String

Private Sub cboProjectCode_AfterUpdate()
'On Error Resume Next
BuildFilter

' If criteria selected to Filter on
If strF <> "" Or Not IsNull(strF) Then
' Assign the filter string to build form filter
Me.Filter = strF
' Turn on form filter
Me.FilterOn = True
Else
' Turn off form filter
Me.FilterOn = False
End If

End Sub
Private Sub cboVendor_AfterUpdate()
'On Error Resume Next
BuildFilter

If strF <> "" Or Not IsNull(strF) Then
Me.Filter = strF
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub
Private Sub BuildFilter()
' Make sure at least one criteria is selected.
If (IsNull(Me.cboProjectCode) Or Me.cboProjectCode = "") And (IsNull(Me.cboVendor) Or Me.cboVendor = "") Then
strF = ""
Else
If Not IsNull(Me.cboProjectCode) And Me.cboProjectCode <> "" Then
strF = "[ProjectCode]='" & Me.cboProjectCode & "'"
Else
strF = "[ProjectCode] LIKE '*' "
End If

If Not IsNull(Me.cboVendor) And Me.cboVendor <> "" Then
strF = "[Vendor]='" & Me.cboVendor & "'"
Else
strF = "[Vendor] LIKE '*' "
End If
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboProjectCode = Null
Me.cboVendor = Null
End Sub[/vba]

HTH

[tammyl]

I've made some changes as mentioned. I no longer get the parameter dialog popup but now nothing happens when I select the project code combo box. I have attached my test database.

Hope this helps resolve my issue.

Thanks tammyl