Preferred Method to Filter Forms/Subforms

[maw230]

As i'm designing this database I'm realizing there are several different ways to filter data on a form or subform, but I'm wondering if there is some reason why one or the other might be preferred.

My use case:
I need to create a form and/or form subform combo where the data is shown in datasheet view and I can apply filters via combo boxes. The best way I've come across to simply view the data so far is to put the data into a subform that is then added to a blank form that is only used to control the subform. Split Form View may work for this as well, but I'm not sure.

To filter the data I had setup my subform's record source as a query with the criteria being the combo boxes on the main form. I wanted the combo boxes to be able to be used in any combination with each other. That is where I ran into problems. It became difficult for me to write the query logic to support this. This is when I started looking into other ways to filter the data.

Then it seems you can simply use the "combo box wizard" to filter your data. I've had mixed results here.

I came across this method in VBA:

Dim strSQL As String
If IsNull(Me.cboShowSup) Then
    ' If the combo is Null, use the whole table as the RecordSource.
    Me.RecordSource = "tblProduct"
Else
    strSQL = "SELECT DISTINCTROW tblProduct.* FROM tblProduct " & _
        "INNER JOIN tblProductSupplier ON " & _
        "tblProduct.ProductID = tblProductSupplier.ProductID " & _
        "WHERE tblProductSupplier.SupplierID = " & Me.cboShowSup & ";"
    Me.RecordSource = strSQL
End If

I started working with it then realized there must be a preferred method based on my use case. One that doesn't involve jumping through as many hoops as I think I have been.

With that said, what are you preferred methods for filtering data on forms or subforms via combo boxes?

 

[pbaldy]

When I'm going to have several optional inputs for the user, I tend to build dynamic SQL as demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

This is basically an expansion on what you have there.

 

[maw230]

When I'm going to have several optional inputs for the user, I tend to build dynamic SQL as demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

This is basically an expansion on what you have there.

And the dynamic SQL becomes your RecordSource for the forms?

 

[pbaldy]

Can be (as it is in that sample), or the method can be used to build a wherecondition for OpenForm/OpenReport.

 

[maw230]

Well I'm giving it a go, but having the usual errors I can't explain. See anything wrong with this code?

Private Sub cmb_Analyst_AfterUpdate()
Dim strSQL As String
If IsNull(Me.cmb_Analyst) Then
    ' If the combo is Null, use the whole table as the RecordSource.
    Me!Main_subform_for_AUView.Form.RecordSource = "Main"
Else
    strSQL = "SELECT Main.* " & _
                "FROM Main " & _
                "WHERE Main.Analyst = " & Chr$(34) & Me.cmb_Analyst & Chr$(34) & ";"
'     Debug.Print strSQL
     [B]Me!Main_subform_for_AUView.Form.RecordSource = strSQL[/B]
End If
Me!Main_subform_for_AUView.Form.Requery
End Sub

The error I'm getting is "Microsoft Access cant find the field 'Main_subform_for_AUView' referred to in your expression. I bolded the line where it occurs. I'm reading it may have something to do with "!" vs "." notations?

 

[pbaldy]

If the subform control on the main form has a different name than the subform itself, it's the name of the control you want there. More here:

http://www.mvps.org/access/forms/frm0031.htm

 

[maw230]

The control (combo box) is on the header of the main form. It's used to filter the results of the subform. I used the link you mentioned when writing this piece:

[B]Me!Main_subform_for_AUView.Form.RecordSource [/B]

Edit: Reading it again that link only refers to referencing controls on subforms, but not the subforms themselves?

There are no master/child links. The main form is simply a blank form used to display the subform in datasheet view. Could this be an issue?

Edit: Well, it would appear that this entire time I was referencing the subform's Source Object (Main_subform_for_AUView) and not the actual name of the subform which is simply "Main"!

All appears well with the world. I've found another piece of code that seems very simple:

Main.Form.Filter = "[Analyst]= '" & cmb_Analyst & "'"
        Main.Form.FilterOn = True

This is working as well and is much easier to implement. I wonder if it will work well with multiple combo boxes where the user can select any combination of them.