filtering a subform

[Brogrim]

I have a subform and I want to control the data displayed depending on a selection of combo boxes and list boxes on the main form.

I am trying this code from a command box on the main form
___________________________________________________________

Private Sub Command17_Click()

Dim strHSERegion As String
Dim strROC As String
Dim strFilter As String

' Build criteria string for HSE Region field
If IsNull(Me.cboHSERegion.Value) Then
strHSERegion = "Like '*'"

Else
strHSERegion = "='" & Me.cboHSERegion.Value & "'"

End If

' Build criteria string for ROC field
If IsNull(Me.lstROC1.Value) Then
strROC = "Like '*'"

Else
strROC = "='" & Me.lstROC1.Value & "'"

End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[HSERegion] " & strHSERegion & " AND [ROC] " & strROC

' Apply the filter and switch it on

With [Forms]![Startup]![sbfrmStartUpMember]
.Filter = strFilter
.FilterOn = True
End With

End Sub
___________________________________________________________

It goes into debug and The line of code

.Filter = strFilter

is highlighted with the message

"Run time error 438"
"Object does'nt support this property or method"
___________________________________________________________

Any help much appreciated

 

[ThomasLafferty]

A filter can probably accomplish what you need, but may not be the best choice. It can be a bit difficult if the complexity of the filter changes, and filters also have a nasty habit of detaching themselves if the user applies a different filter since the new filter can be saved with the form.

Why not build a query referencing the checkboxes etc on your main form and apply and remove the query using a toggle button on your form?

Give yourself a toggle button, name it btn_ApplyFilter and set the following code in the AfterUpdate event.

Sub btn_ApplyFilter_AfterUpdate()
On Error Goto Err_btn_ApplyFilter_AfterUpdate

If Me.btn_ApplyFilter.Value = -1     'User applies filter
     Me.btn_ApplyFilter.Caption = "Remove Filter"
     Me.Recordsource = "[!]YourQueryName[/!]"
Else
     Me.btn_ApplyFilter.Caption = "Apply Filter"
     Me.Recordsource = "[!]YourTableName[/!]"
End If

Exit_btn_ApplyFilter_AfterUpdate:
     End Sub

Err_btn_ApplyFilter_AfterUpdate:
     Msgbox Err.Description
     Resume Exit_btn_ApplyFilter_AfterUpdate
End Sub

You will, of course, have to change the items in red above to match your case. Hope this helps...

Tom

Live once die twice; live twice die once.

 

[PHV]

With [Forms]![Startup]![sbfrmStartUpMember][!].Form[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886