I am trying to filter the sub form using 3 combo boxes (or may be more) in my 
main form.
I tried few codes but that didn’t work. 

Here is the sample code I am trying but it filters using last combo box 
selection but not all.
Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
Me!subform2.Form.FilterOn = True
Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
Me!subform2.Form.FilterOn = True

My sub form is bound to the main Exception Table – (All Exceptions Records – 
ClientID is FK, Dept – FK, Exception Type – FK, .. and have duplicates)
This combo boxes are bound to the unique –PK- No Dups - tables (Client, 
Department, Exceptions,etc)

So to explain in sql it should do like this – Select * from subform2(’s 
recordsource) where subform.[Exception Type] = MainfForm.cboException   AND  
subform.[Document Dept] = MainForm.cboDept 

Thanks for your help and time.

On Wed, 30 Dec 2009 07:35:01 -0800, kay
<kay@discussions.microsoft.com> wrote:

Just like you're concatenating the various parts of the WHERE clause
in SQL, so also do you need to do the same with the string that will
become the Filter expression. Something like:
dim strFilter as string
strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
[Document Dept] = '" & Me![cboDept] & "'"
Me!subform2.Form.Filter = strFilter

You can also add code to check if a combobox did not have a selection,
and omit that clause from strFilter:
If Not IsNull(Me.myCombo.Value) Then
  strFilter = strFilter & " AND myField = " & Me.myCombo
End If
If Not IsNull(Me.myOtherCombo.Value) Then
  strFilter = strFilter & " AND myOtherField = " & Me.myOtherCombo
End If

-Tom.
Microsoft Access MVP


>I am trying to filter the sub form using 3 combo boxes (or may be more) in my 
>main form.
>I tried few codes but that didn�t work. 
>
>Here is the sample code I am trying but it filters using last combo box 
>selection but not all.
>Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
>Me!subform2.Form.FilterOn = True
>Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
>Me!subform2.Form.FilterOn = True
>
>My sub form is bound to the main Exception Table � (All Exceptions Records � 
>ClientID is FK, Dept � FK, Exception Type � FK, .. and have duplicates)
>This combo boxes are bound to the unique �PK- No Dups - tables (Client, 
>Department, Exceptions,etc)
>
>So to explain in sql it should do like this � Select * from subform2(�s 
>recordsource) where subform.[Exception Type] = MainfForm.cboException   AND  
>subform.[Document Dept] = MainForm.cboDept 
>
>Thanks for your help and time.

I tried this code but does not filter anything.
What I did , I created button - cmdFilter and then onclick event I paste 
this code ***strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
[Document Dept] = '" & Me![cboDept] & "'"
Me!subform2.Form.Filter = strFilter***

its not giving any error but on click it's not doing any action.



"Tom van Stiphout" wrote:

> On Wed, 30 Dec 2009 07:35:01 -0800, kay
> <kay@discussions.microsoft.com> wrote:
> 
> Just like you're concatenating the various parts of the WHERE clause
> in SQL, so also do you need to do the same with the string that will
> become the Filter expression. Something like:
> dim strFilter as string
> strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
> [Document Dept] = '" & Me![cboDept] & "'"
> Me!subform2.Form.Filter = strFilter
> 
> You can also add code to check if a combobox did not have a selection,
> and omit that clause from strFilter:
> If Not IsNull(Me.myCombo.Value) Then
>   strFilter = strFilter & " AND myField = " & Me.myCombo
> End If
> If Not IsNull(Me.myOtherCombo.Value) Then
>   strFilter = strFilter & " AND myOtherField = " & Me.myOtherCombo
> End If
> 
> -Tom.
> Microsoft Access MVP
> 
> 
> >I am trying to filter the sub form using 3 combo boxes (or may be more) in my 
> >main form.
> >I tried few codes but that didn’t work. 
> >
> >Here is the sample code I am trying but it filters using last combo box 
> >selection but not all.
> >Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
> >Me!subform2.Form.FilterOn = True
> >Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
> >Me!subform2.Form.FilterOn = True
> >
> >My sub form is bound to the main Exception Table – (All Exceptions Records – 
> >ClientID is FK, Dept – FK, Exception Type – FK, .. and have duplicates)
> >This combo boxes are bound to the unique –PK- No Dups - tables (Client, 
> >Department, Exceptions,etc)
> >
> >So to explain in sql it should do like this – Select * from subform2(’s 
> >recordsource) where subform.[Exception Type] = MainfForm.cboException   AND  
> >subform.[Document Dept] = MainForm.cboDept 
> >
> >Thanks for your help and time.
> .
> 

Do you really need to filter?  You could use the master-child  relationships 
in your form-subform.

The subform is linked to the main form by Master-Child Links.  The wizard 
gives you one option if it recognizes key fields and matching names

If you choose to 'Define My Own'  you could set all three combo boxes in the 
main form to link to the appropriate fields in your subform

Anytime you change one of  the linked controls on the main form the subform 
should repopluate appropriately

If the wizard doesn't give you enough links you can go to the properties of 
the sub form, data tab and set child and master fields there

I don't know what the limit is on the number of master-child fields you can 
choose. Anyone know this?

Tom

"kay" <kay@discussions.microsoft.com> wrote in message 
news:A2F130FA-D390-4F0B-9175-5D8796213482@microsoft.com...
>I am trying to filter the sub form using 3 combo boxes (or may be more) in 
>my
> main form.
> I tried few codes but that didn't work.
>
> Here is the sample code I am trying but it filters using last combo box
> selection but not all.
> Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
> Me!subform2.Form.FilterOn = True
> Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
> Me!subform2.Form.FilterOn = True
>
> My sub form is bound to the main Exception Table - (All Exceptions 
> Records -
> ClientID is FK, Dept - FK, Exception Type - FK, .. and have duplicates)
> This combo boxes are bound to the unique -PK- No Dups - tables (Client,
> Department, Exceptions,etc)
>
> So to explain in sql it should do like this - Select * from subform2('s
> recordsource) where subform.[Exception Type] = MainfForm.cboException 
> AND
> subform.[Document Dept] = MainForm.cboDept
>
> Thanks for your help and time.
> 

On Wed, 30 Dec 2009 10:14:01 -0800, kay
<kay@discussions.microsoft.com> wrote:

Did you forget:
Me!subform2.Form.FilterOn = True

-Tom.
Microsoft Access MVP


>I tried this code but does not filter anything.
>What I did , I created button - cmdFilter and then onclick event I paste 
>this code ***strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
>[Document Dept] = '" & Me![cboDept] & "'"
>Me!subform2.Form.Filter = strFilter***
>
>its not giving any error but on click it's not doing any action.
>
>
>
>"Tom van Stiphout" wrote:
>
>> On Wed, 30 Dec 2009 07:35:01 -0800, kay
>> <kay@discussions.microsoft.com> wrote:
>> 
>> Just like you're concatenating the various parts of the WHERE clause
>> in SQL, so also do you need to do the same with the string that will
>> become the Filter expression. Something like:
>> dim strFilter as string
>> strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
>> [Document Dept] = '" & Me![cboDept] & "'"
>> Me!subform2.Form.Filter = strFilter
>> 
>> You can also add code to check if a combobox did not have a selection,
>> and omit that clause from strFilter:
>> If Not IsNull(Me.myCombo.Value) Then
>>   strFilter = strFilter & " AND myField = " & Me.myCombo
>> End If
>> If Not IsNull(Me.myOtherCombo.Value) Then
>>   strFilter = strFilter & " AND myOtherField = " & Me.myOtherCombo
>> End If
>> 
>> -Tom.
>> Microsoft Access MVP
>> 
>> 
>> >I am trying to filter the sub form using 3 combo boxes (or may be more) in my 
>> >main form.
>> >I tried few codes but that didn�t work. 
>> >
>> >Here is the sample code I am trying but it filters using last combo box 
>> >selection but not all.
>> >Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
>> >Me!subform2.Form.FilterOn = True
>> >Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
>> >Me!subform2.Form.FilterOn = True
>> >
>> >My sub form is bound to the main Exception Table � (All Exceptions Records � 
>> >ClientID is FK, Dept � FK, Exception Type � FK, .. and have duplicates)
>> >This combo boxes are bound to the unique �PK- No Dups - tables (Client, 
>> >Department, Exceptions,etc)
>> >
>> >So to explain in sql it should do like this � Select * from subform2(�s 
>> >recordsource) where subform.[Exception Type] = MainfForm.cboException   AND  
>> >subform.[Document Dept] = MainForm.cboDept 
>> >
>> >Thanks for your help and time.
>> .
>> 

thanks, yes I forgot to put that.
It is working fine now.


"Tom van Stiphout" wrote:

> On Wed, 30 Dec 2009 10:14:01 -0800, kay
> <kay@discussions.microsoft.com> wrote:
> 
> Did you forget:
> Me!subform2.Form.FilterOn = True
> 
> -Tom.
> Microsoft Access MVP
> 
> 
> >I tried this code but does not filter anything.
> >What I did , I created button - cmdFilter and then onclick event I paste 
> >this code ***strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
> >[Document Dept] = '" & Me![cboDept] & "'"
> >Me!subform2.Form.Filter = strFilter***
> >
> >its not giving any error but on click it's not doing any action.
> >
> >
> >
> >"Tom van Stiphout" wrote:
> >
> >> On Wed, 30 Dec 2009 07:35:01 -0800, kay
> >> <kay@discussions.microsoft.com> wrote:
> >> 
> >> Just like you're concatenating the various parts of the WHERE clause
> >> in SQL, so also do you need to do the same with the string that will
> >> become the Filter expression. Something like:
> >> dim strFilter as string
> >> strFilter = "[Exception Type] = '" & Me![cboException] & "' AND
> >> [Document Dept] = '" & Me![cboDept] & "'"
> >> Me!subform2.Form.Filter = strFilter
> >> 
> >> You can also add code to check if a combobox did not have a selection,
> >> and omit that clause from strFilter:
> >> If Not IsNull(Me.myCombo.Value) Then
> >>   strFilter = strFilter & " AND myField = " & Me.myCombo
> >> End If
> >> If Not IsNull(Me.myOtherCombo.Value) Then
> >>   strFilter = strFilter & " AND myOtherField = " & Me.myOtherCombo
> >> End If
> >> 
> >> -Tom.
> >> Microsoft Access MVP
> >> 
> >> 
> >> >I am trying to filter the sub form using 3 combo boxes (or may be more) in my 
> >> >main form.
> >> >I tried few codes but that didn’t work. 
> >> >
> >> >Here is the sample code I am trying but it filters using last combo box 
> >> >selection but not all.
> >> >Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
> >> >Me!subform2.Form.FilterOn = True
> >> >Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
> >> >Me!subform2.Form.FilterOn = True
> >> >
> >> >My sub form is bound to the main Exception Table – (All Exceptions Records – 
> >> >ClientID is FK, Dept – FK, Exception Type – FK, .. and have duplicates)
> >> >This combo boxes are bound to the unique –PK- No Dups - tables (Client, 
> >> >Department, Exceptions,etc)
> >> >
> >> >So to explain in sql it should do like this – Select * from subform2(’s 
> >> >recordsource) where subform.[Exception Type] = MainfForm.cboException   AND  
> >> >subform.[Document Dept] = MainForm.cboDept 
> >> >
> >> >Thanks for your help and time.
> >> .
> >> 
> .
> 

thanks chegu tom for the suggestion.


"Chegu Tom" wrote:

> Do you really need to filter?  You could use the master-child  relationships 
> in your form-subform.
> 
> The subform is linked to the main form by Master-Child Links.  The wizard 
> gives you one option if it recognizes key fields and matching names
> 
> If you choose to 'Define My Own'  you could set all three combo boxes in the 
> main form to link to the appropriate fields in your subform
> 
> Anytime you change one of  the linked controls on the main form the subform 
> should repopluate appropriately
> 
> If the wizard doesn't give you enough links you can go to the properties of 
> the sub form, data tab and set child and master fields there
> 
> I don't know what the limit is on the number of master-child fields you can 
> choose. Anyone know this?
> 
> Tom
> 
> "kay" <kay@discussions.microsoft.com> wrote in message 
> news:A2F130FA-D390-4F0B-9175-5D8796213482@microsoft.com...
> >I am trying to filter the sub form using 3 combo boxes (or may be more) in 
> >my
> > main form.
> > I tried few codes but that didn't work.
> >
> > Here is the sample code I am trying but it filters using last combo box
> > selection but not all.
> > Me!subform2.Form.Filter = "[Exception Type] = '" & Me![cboException] & "'"
> > Me!subform2.Form.FilterOn = True
> > Me!subform2.Form.Filter = "[Document Dept] = '" & Me![cboDept] & "'"
> > Me!subform2.Form.FilterOn = True
> >
> > My sub form is bound to the main Exception Table - (All Exceptions 
> > Records -
> > ClientID is FK, Dept - FK, Exception Type - FK, .. and have duplicates)
> > This combo boxes are bound to the unique -PK- No Dups - tables (Client,
> > Department, Exceptions,etc)
> >
> > So to explain in sql it should do like this - Select * from subform2('s
> > recordsource) where subform.[Exception Type] = MainfForm.cboException 
> > AND
> > subform.[Document Dept] = MainForm.cboDept
> >
> > Thanks for your help and time.
> > 
> 
> 
> .
>