Reference a Combo Box Column to apply filter to form

Question

Hi,

Im using Access 2010. I have a continuous form in which there is a combo box (cboTemp) and another text field: ComponentName which has control source as cboTemp.Column(3) to display info from cboTemp.

I also have a set of text boxes in the form header which I use to filter the records in the form. The user enters the desired text in the Filter text boxes and the form applyfilter method fetches the matching records.

My challenge is that I cannot get Access to reference the columns in the combo box (other than column 0) to apply the filter.

Ive tried this:

stWHERE = stWHERE & "AND ComponentName = " & "'" & Me.FilterItemType & "'" & "" but it doesnt work.

I need something like this to work:

stWHERE = stWHERE & "AND cboTemp.Column(3) = " & "'" & Me.FilterItemType & "'" & ""

DoCmd.ApplyFilter , stWHERE

Thx,

Anand

Answer 1

How about?

stWHERE = stWHERE & " AND " & Me.cboTemp.Column(3) & "='" & Me.FilterItemType & "'"

Answer 2

stWHERE = stWHERE & "AND cboTemp.Column(3) = " & "'" & Me.FilterItemType & "'"

The way that's written would mean that the current value in the fourth column in the combo box's RowSource is not data, but the name of a column of text data type in the form's recordset, and you want the rows returned in the form to be returned on the basis of the current value in a FilterItemType control.  If that's the case then the DBguy's solution should work.  If not please explain in more detail what you are aiming at.

---

Ken Sheridan, Stafford, England

Answer 3

Hi,

Very sorry I had not checked back here and hadnt seen the responses. 

Ken, 

The value in the fourth column of the combo box's row source contains data - not the name of a column!

I want Access 2010 to match this data (in the fourth column) to the value in the FilterItemType control selected by the user and filter the form.

The solution suggested by theDGguy does not work.

Thanks,

Anand

Answer 4

Hi Anand,

Can you show us an image or a sample of what your form data looks like? What is the actual value in the combo and the FilterItemType control?

Answer 5

I don't fully understand what you are attempting here.  A form is filtered to row in which the value in a column in the form's recordset matches a parameter value.  If the BoundColumn property of the combo box is 4 then you can filter on the column to which the combo box is bound, but I doubt that's the case.  To allow the form to be filtered on whatever attribute the fourth column of the combo box represents you will need to bring this attribute into the form's recordset by making its RecordSource property a query which returns the column in question.  Taking one of my online demos as an example, let's assume you have a form of locations and a combo box which lists the parish, district and county for each location.  To allow the form to be filtered by county its RecordSource would need to be:

SELECT Counties.County, Districts.District, Parishes.Parish,
Locations.Location, Parishes.ParishID
FROM ((Counties INNER JOIN Districts
ON Counties.CountyID = Districts.CountyID)
INNER JOIN Parishes ON Districts.DistrictID = Parishes.DistrictID)
INNER JOIN Locations ON Parishes.ParishID = Locations.ParishID;

Simply basing the form on the Locations table and returning the county in an unbound text box which references a column of a combo box bound to the foreign key ParishID column would only allow you to filter the form by district or county if a subquery were included in the expression the filter property.  This is possible, but returning the County column from the referenced Counties table in the query is far simpler.

---

Ken Sheridan, Stafford, England