Filter subform with combos on main

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a subform in datasheet view which I want to filter based on the
choices entered un several unbound combo boxes on the main form. Once I
have built a strFilter from the ichoices selected in the combos; how do I
apply the filter to the subform? Whenever I try something like
Forms!MainForm!Subform.form.filter = strFilter I get an error stating I
cannot set the value. Any suggestions?
 
What is the error number?

If the subform control really is named "subform", that could cause a
problem. Try renaming it, by right-clicking the edge of it, choosing
Properties, and setting its Name (Other tab).
 
Here's the actual code I'm using. With this code I don't actually get any
error message now. However; the filter does not appear to be applied. I am
calling the subform filter routine from the combo box on the main form after
update event.

When I view the subform in design mode after running the code the filter on
the data tab is blank. Is there something wrong with the syntax of my filter
update or something like having to have the focus in the subform before it
will work?

Forms!PTRVIEW!PTRsubform.Form.Filter = strFilter
Forms!PTRVIEW!PTRsubform.Form.FilterOn = True
 
Opps! Correction; the error popped up again on subsequent testing. The
error is number 2448 "You can't assign a value to this object"
 
Oaky, thos this is intermittent?

That suggests either a naming issue, possibly caused by Name AutoCorrect.
Details:
http://allenbrowne.com/bug-03.html

Alternatively, the string you are attempting to assign could be mal-formed,
e.g. missing the quotes delimiting text, or the # delimiting dates, or
something else that does not make sense in the string.
 
Here's what I have accomplished so far. By placing an
unbound text box on the main form named ActualT1Control
and placing the formula "=T1Control.column(1) in it the
following sub runs correctly. However; when replacing the
reference ActualT1Control with the expression InControl
the parameter box appears looking for the value of
InControl.

I wonder why I have to place the ActualT1Control object on
the main form rather than calling the column(1) property
from a function.


Private Sub BuildFilterString()

Dim frm As Form, strFilter As String, InControl As
String

Set frm = Forms!PTRView!MyPTRsubform.Form
strFilter = ""
strFilter = strFilter & "(T1Control = '" &
ActualT1Control & "')"
frm.Filter = strFilter
frm.FilterOn = True
End Sub

Private Function GetT1Control()
Dim InControl As String

InControl = Me.cmboT1control.Column(1)
End Function
 
The Column() property is zero based, i.e. the first column is Column(0).

That means that Column(1) actually refers to the 2nd column of the combo.
Unless you changed the combo's Bound Column property, its value is
Column(0), which would explain why that acts differently from the Column(1)
reference.
 
I suspect the issue relates to scope. Tried changing the
bound column on the combo boxes without success. After
several hours this weekend attempting to resolve the issue
I have generated the following code:

Option Compare Database
Option Explicit

Private Sub BuildFilterString()

Dim frm As Form, strFilter As String
Dim T1Control As String
Dim T1Partner As String
Dim T1Manager As String
Dim T1Preparer As String

Set frm = MyPTRsubform.Form
strFilter = ""

If cmboT1control <> 0 Then strFilter = strFilter
& "(T1Control = '" & T1Control.Column(1) & "') AND "
If cmboT1Partner <> 0 Then strFilter = strFilter
& "(ContactPartner.StaffGivenName = '" & ActualT1Partner
& "') AND "
If cmboT1Manager <> 0 Then strFilter = strFilter
& "(ContactManager.StaffGivenName = '" & ActualT1Manager
& "') AND "
If cmboT1Preparer <> 0 Then strFilter = strFilter
& "(T1Preparer.StaffGivenName = '" & ActualT1Preparer
& "')"

If Right(strFilter, 5) = " AND " Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If

frm.Filter = strFilter
frm.FilterOn = True

End Sub
Private Sub cmboT1control_AfterUpdate()
BuildFilterString
End Sub
Private Sub cmboT1Manager_AfterUpdate()
BuildFilterString
End Sub
Private Sub cmboT1Partner_AfterUpdate()
BuildFilterString
End Sub
Private Sub cmboT1Preparer_AfterUpdate()
BuildFilterString
End Sub

You will note the first "If" line for the filter attempts
to use T1Control.Column(1) This does not work. Get a
Compile error: Invalid qualfier

Perhaps a Function is required to supply the .Column(1)
data.

By replacing the reference to T1Control.Column(1) with
ActualT1Control (a text box on the main form with the
formula = T1Control.column(1) the filter works as expected.

Puzzling to say the least.
 
Hi Rick

I'm not sure what else to suggest.

The fact that it works when you make an indirect reference (through another
text box), suggests that it may be a timing issue (resolved by forcing
Access to evaluate the Column(1) first), or else a data type issue (where
the data type is correctly recognised in the text box, but not in the
Column() collection.)

If it's a timing issue, a Recalc may help.
If it's a data type issue, typecasting may help. More on this at:
http://allenbrowne.com/ser-45.html
 
Hi Allen:

Thanks for all your input. After tooling around with the issue for a while
longer I noted the unbound text box and the related label bore the exact same
names which Access must do when you use the wizard to create the text box.
In any event; I changed the name of the text box to cmboT1Control and the
reference to T1Control.column(1) in the code now works fine. Seems the prior
reference was ambiguous causing the error.

Many thanks for your interest in this problem and the feedback that was very
helpful in isolating the "bug".

Regards,

Rick in N S
 
Back
Top