Filtering by combo does not work

[Guest]

Hello experts:

Here is the data property of my combo box:

Control Source: SupName
Row source Type: Table/Query
Rouw Source: SELECT Suppliers.SupName FROM Suppliers;
Bound Column: 1

Here is my event property on Click:
Private Sub SupName_Click()
Filter = "[SupName]"
FilterOn = True

The above does not work on my filtering by combo box.

This is to filter by supplier in my main form. I plan not to use filtering
by form on the tool bar, because I plan to have the next filter in my subform
using similar to Northwind of access of "Customer Phone List" which is using
Macro Phone list,

Thanks in advance for any help provided.

Frank

 

[Ken Snell \(MVP\)]

The string value that you give to the filter must include the field name and
the value for that field name. Assuming that SupName is the name of the
field, and the name of the combo box:

Filter = "SupName=" & Me!SupName.Value

The above is used if the combo box's bound column contains a numeric value.
If it's a text value:

Filter = "SupName='" & Me!SupName.Value & "'"


You probably are going to have a problem with this form for another reason.
It appears that you are naming the combo box the same as the SupName field,
and you have the combo box bound to the SupName field. This means that the
value of the SupName field for the current record is going to be changed to
the value you select in the combo box, and then the form's filter "may"
work. However, the form is going to have a problem telling the difference
between the field named SupName and the combo box named SupName. If you want
to use the combo box just for setting a filter, its Control Source should be
empty (unbound), and you should name the combo box something like
cboSupName, or cboSupNameFilter.

Also, rather than use the Click event of the combo box, use the AfterUpdate
event of the combo box to set the filter for the form.

 

[AccessVandal via AccessMonster.com]

Hi Frank,

Take a look at this link from Allen Browne

http://allenbrowne.com/ser-28.html

Use the afterupdate event of the combo box, don't use the on click event.

Frank Situmorang wrote:
Hello experts:

Here is the data property of my combo box:

Control Source: SupName
Row source Type: Table/Query
Rouw Source: SELECT Suppliers.SupName FROM Suppliers;
Bound Column: 1

Here is my event property on Click:
Private Sub SupName_Click()
Filter = "[SupName]"
FilterOn = True

The above does not work on my filtering by combo box.

This is to filter by supplier in my main form. I plan not to use filtering
by form on the tool bar, because I plan to have the next filter in my subform
using similar to Northwind of access of "Customer Phone List" which is using
Macro Phone list,

Thanks in advance for any help provided.

Frank

 

[Guest]

Ken,

I have followed your instruction but it seems has not worked. This is my VBA:
Private Sub CboSupName_AfterUpdate()
Filter = "CboSupName='" & Me!SupName.Value & "'"
FilterOn = True
End Sub

For your info, this form with the subform perfeclty works using filter by
form on the tool bar.

Could you please help me?

Frank

The string value that you give to the filter must include the field name and
the value for that field name. Assuming that SupName is the name of the
field, and the name of the combo box:

Filter = "SupName=" & Me!SupName.Value

The above is used if the combo box's bound column contains a numeric value.
If it's a text value:

Filter = "SupName='" & Me!SupName.Value & "'"


You probably are going to have a problem with this form for another reason.
It appears that you are naming the combo box the same as the SupName field,
and you have the combo box bound to the SupName field. This means that the
value of the SupName field for the current record is going to be changed to
the value you select in the combo box, and then the form's filter "may"
work. However, the form is going to have a problem telling the difference
between the field named SupName and the combo box named SupName. If you want
to use the combo box just for setting a filter, its Control Source should be
empty (unbound), and you should name the combo box something like
cboSupName, or cboSupNameFilter.

Also, rather than use the Click event of the combo box, use the AfterUpdate
event of the combo box to set the filter for the form.

--

Ken Snell
<MS ACCESS MVP>

Hello experts:

Here is the data property of my combo box:

Control Source: SupName
Row source Type: Table/Query
Rouw Source: SELECT Suppliers.SupName FROM Suppliers;
Bound Column: 1

Here is my event property on Click:
Private Sub SupName_Click()
Filter = "[SupName]"
FilterOn = True

The above does not work on my filtering by combo box.

This is to filter by supplier in my main form. I plan not to use filtering
by form on the tool bar, because I plan to have the next filter in my
subform
using similar to Northwind of access of "Customer Phone List" which is
using
Macro Phone list,

Thanks in advance for any help provided.

Frank

 

[Guest]

Hi... Accerss Vandal...I have read the website you provided, but do you think
we need the innerjoin?, because we already have the link in my table, which
the invoice table is the parent while supplier table is child, so I can see
the invoices details for selected supplier in the filtering by combo.

My concern now is to make combo fiterling supplier in the mainform (without
using filtering by form of toolbar) and afterthat I want to filter the
subform to showup only the outstanding inovicess the 2nd one is Paid invoices
and the default is show all using toggle buttons in the option group of
subform header. I need these features because I use continous form, other
wise it will be very2 long downward browsing.

Frank

Appreciate your help

Hi Frank,

Take a look at this link from Allen Browne

http://allenbrowne.com/ser-28.html

Use the afterupdate event of the combo box, don't use the on click event.

Frank Situmorang wrote:
Hello experts:

Here is the data property of my combo box:

Control Source: SupName
Row source Type: Table/Query
Rouw Source: SELECT Suppliers.SupName FROM Suppliers;
Bound Column: 1

Here is my event property on Click:
Private Sub SupName_Click()
Filter = "[SupName]"
FilterOn = True

The above does not work on my filtering by combo box.

This is to filter by supplier in my main form. I plan not to use filtering
by form on the tool bar, because I plan to have the next filter in my subform
using similar to Northwind of access of "Customer Phone List" which is using
Macro Phone list,

Thanks in advance for any help provided.

Frank

 

[AccessVandal via AccessMonster.com]

Hi Frank,

Try to change the field name from

Filter = "CboSupName='" & Me!SupName.Value & "'"

To

Filter = "SupName='" & Me!CboSupName.Value & "'"

"SupName" was the field name of the Record source and "CboSupName" is the
form's control name.
Have I got it right?

Frank Situmorang wrote:
Hi... Accerss Vandal...I have read the website you provided, but do you think
we need the innerjoin?, because we already have the link in my table, which
the invoice table is the parent while supplier table is child, so I can see
the invoices details for selected supplier in the filtering by combo.

My concern now is to make combo fiterling supplier in the mainform (without
using filtering by form of toolbar) and afterthat I want to filter the
subform to showup only the outstanding inovicess the 2nd one is Paid invoices
and the default is show all using toggle buttons in the option group of
subform header. I need these features because I use continous form, other
wise it will be very2 long downward browsing.

Frank

Appreciate your help

Hi Frank,

[quoted text clipped - 29 lines]

 

[Ken Snell \(MVP\)]

You've reversed the field and combo box names in the filter string:

Private Sub CboSupName_AfterUpdate()
Filter = "SupName='" & Me!CboSupName.Value & "'"
FilterOn = True
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ken,

I have followed your instruction but it seems has not worked. This is my
VBA:
Private Sub CboSupName_AfterUpdate()
Filter = "CboSupName='" & Me!SupName.Value & "'"
FilterOn = True
End Sub

For your info, this form with the subform perfeclty works using filter by
form on the tool bar.

Could you please help me?

Frank

The string value that you give to the filter must include the field name
and
the value for that field name. Assuming that SupName is the name of the
field, and the name of the combo box:

Filter = "SupName=" & Me!SupName.Value

The above is used if the combo box's bound column contains a numeric
value.
If it's a text value:

Filter = "SupName='" & Me!SupName.Value & "'"


You probably are going to have a problem with this form for another
reason.
It appears that you are naming the combo box the same as the SupName
field,
and you have the combo box bound to the SupName field. This means that
the
value of the SupName field for the current record is going to be changed
to
the value you select in the combo box, and then the form's filter "may"
work. However, the form is going to have a problem telling the difference
between the field named SupName and the combo box named SupName. If you
want
to use the combo box just for setting a filter, its Control Source should
be
empty (unbound), and you should name the combo box something like
cboSupName, or cboSupNameFilter.

Also, rather than use the Click event of the combo box, use the
AfterUpdate
event of the combo box to set the filter for the form.

--

Ken Snell
<MS ACCESS MVP>

Hello experts:

Here is the data property of my combo box:

Control Source: SupName
Row source Type: Table/Query
Rouw Source: SELECT Suppliers.SupName FROM Suppliers;
Bound Column: 1

Here is my event property on Click:
Private Sub SupName_Click()
Filter = "[SupName]"
FilterOn = True

The above does not work on my filtering by combo box.

This is to filter by supplier in my main form. I plan not to use
filtering
by form on the tool bar, because I plan to have the next filter in my
subform
using similar to Northwind of access of "Customer Phone List" which is
using
Macro Phone list,

Thanks in advance for any help provided.

Frank