Filter form using multiple combo boxes selection

  • Thread starter Thread starter kay
  • Start date Start date
K

kay

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

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 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 said:
On Wed, 30 Dec 2009 07:35:01 -0800, kay

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.
Click to expand...
.
Click to expand...
 
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
 
On Wed, 30 Dec 2009 10:14:01 -0800, kay

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 said:
On Wed, 30 Dec 2009 07:35:01 -0800, kay

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.
Click to expand...
.
Click to expand...
Click to expand...
 
thanks chegu tom for the suggestion.


Chegu Tom said:
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 said:
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.
Click to expand...


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


Tom van Stiphout said:
On Wed, 30 Dec 2009 10:14:01 -0800, kay

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 said:
On Wed, 30 Dec 2009 07:35:01 -0800, kay

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.
.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top