Filter form using multiple combo boxes selection

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.
 
T

Tom van Stiphout

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
 
K

kay

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...
 
C

Chegu Tom

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
 
T

Tom van Stiphout

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...
 
K

kay

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...
 
K

kay

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering Subform 10
Referencing Data in One Subform From Another Subform? 2
Selecting current record in subform after removing filter 1
Repopulating form with combo boxes in subform 1
Display Text based on Selection from Combo Box 3
Form/Subform Synchronization 5
filter by multiple combo boxes 2
See more fields after selection of particular item in combo 3

Top