Problem filtering a combobox with textbox input

[RHooper]

Hi,

I'm new to Access, so I apologize if this question is trivial.

I am trying to set-up a quick filter for users to define on a form bound to a table. I have a combo box called cboSearchCriteria from which users can select the field they wish to filter by and a text box called txtSearch into which they can specify criteria. The user then clicks a button to filter:

Private Sub cmdFilter_Click()
 
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
 
    If cboSearchCriteria = "TR #" Then
        If Not IsNull(Me.txtSearch) Then
            strWhere = strWhere & "([TR#] Like ""*" & Me.txtSearch & "*"") AND "
        End If
    ElseIf cboSearchCriteria = "TR Description" Then
        If Not IsNull(Me.txtSearch) Then
            strWhere = strWhere & "([ccDescription] Like ""*" & Me.txtSearch & "*"") AND "
        End If
    ElseIf cboSearchCriteria = "Project Manager" Then
        If Not IsNull(Me.txtSearch) Then
            strWhere = strWhere & "([ccProjManID] Like ""*" & Me.txtSearch & "*"") AND "
        End If
 
       lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
 

My problem is with filtering the combo boxes. The text boxes (for TR# and ccDescription) work well, but when I enter a value into txtSearch when trying to filter combo boxes (for ccProjManID and ccSignManID), it looks for a value from the wrong column of the combo box and I don't know how to change this in my code. It asks for the bound column number, when I want it to ask for the text string associated with it.

To explain further: the ccProjManID combo box is called cboPMID. ccProjManID is the control source, from table cc, and is built with a query from another table called Employee with:

SELECT DISTINCTROW Employee.EmployeeID, Employee.EMName FROM Employee ORDER BY Employee.EMName;

Column Count = 2
Column Widths = 0";1"
Bound Column = 1

I hope this is clear - if not, please ask. I would appreciate any help anyone can give as this problem seems like it would have a simple solution, but I can't find any way to change this (especially with my limited coding experience).

Thanks,

Richard

[Stewart Ross]

Hi. You can refer to any column in a combo by using the Column(n) qualifier after the name of the combo. Columns are numbered from 0, so to refer to the second column you refer to Column(1) as follows:

Me![cboPMID].Column(1)

The column normally bound is Column(0), and this is the default column returned when you leave off the column qualifier, as you have found.

-Stewart

[RHooper]

Thanks for the reply, Stewart.

I'd messed around with this before and couldn't get it working. I think that the problem with using this is that my filter code doesn't refer to the combobox (cboPMID), but to the field ccProjManID in my CC table.

Is there a way to use this column reference when referring to ccProjManID? It also has two columns, an employee ID number and an employee name. My text box search will only look in the bound column, though - the ID number. It searches just fine if i type in 11 or something, but I want the user to able to type in the name.

Thanks,

Richard

Hi. You can refer to any column in a combo by using the Column(n) qualifier after the name of the combo. Columns are numbered from 0, so to refer to the second column you refer to Column(1) as follows:

Me![cboPMID].Column(1)

The column normally bound is Column(0), and this is the default column returned when you leave off the column qualifier, as you have found.

-Stewart

[RHooper]

Nevermind - I think I've solved my problem. I found out that I can refer to the desired combo box column using Lookup_cboPMID.EMName

I'm sure there's a way to refer to a column of the table field, but this works and I've been stuck on this for far too long.

Thanks for the reply, Stewart.

I'd messed around with this before and couldn't get it working. I think that the problem with using this is that my filter code doesn't refer to the combobox (cboPMID), but to the field ccProjManID in my CC table.

Is there a way to use this column reference when referring to ccProjManID? It also has two columns, an employee ID number and an employee name. My text box search will only look in the bound column, though - the ID number. It searches just fine if i type in 11 or something, but I want the user to able to type in the name.

Thanks,

Richard

[Stewart Ross]

Hi Richard. Must admit that I just cannot find any reference to the combo you mention in your code at all.

I think you are confusing filtering the recordsource of your form with accessing the combo box. The filter for the form can only refer to the fields on the query or table which are included in its recordsource. It cannot refer to controls on the form, although you can include values from form controls as items to compare (as you have throughout, for example in line 16 with txtSearch:

strWhere = strWhere & "([ccProjManID] Like ""*" & Me.txtSearch & "*"") AND "

There appears to be no reference to cboPMID in your code. Line 16 above is testing the project manager ID field in your recordsource against the non-combo textbox contents – which will indeed have to be an ID, not the name of a person.

Could you explain further how cboPMID relates to the txtSearch contents, and what you expect to happen when a user selects a name from the combo?

Thanks

Stewart

[RHooper]

Hi Stewart,

I think you're right about what I was confused about, and this probably made my initial question rather difficult to understand.

What I really should have said is that I wanted to have the text entered by the user in txtSearch used to filter the recordsource by ccProjManID, which is shown in the form by means of a combobox with two columns called cboPMID.

I had this filter setup with the code you mentioned:

strWhere = strWhere & "([ccProjManID] Like ""*" & Me.txtSearch & "*"") AND "

However, my problem was that ccProjManID is made up of two values: a text name and a numeric identifier, with the numeric identifier being the bound column. When I entered a name into txtSearch to filter by, it was looking in the numeric identifier of ccProjManID instead of the text name.

As I mentioned, my solution was:

strWhere = strWhere & "(Lookup_cboPMID.EMName Like ""*" & Me.txtSearch & "*"") AND "

(with EMName being the text name I wished to search by) So, unless I'm mistaken (again), this is filtering by the combo box and not the control source value. I hope this is a bit clearer.

FYI, both the cboPMID combobox and ccProjManID in the control source use the same row source:

SELECT DISTINCTROW Employee.EmployeeID, Employee.EMName FROM Employee;

Hi Richard. Must admit that I just cannot find any reference to the combo you mention in your code at all.

I think you are confusing filtering the recordsource of your form with accessing the combo box. The filter for the form can only refer to the fields on the query or table which are included in its recordsource. It cannot refer to controls on the form, although you can include values from form controls as items to compare (as you have throughout, for example in line 16 with txtSearch:

strWhere = strWhere & "([ccProjManID] Like ""*" & Me.txtSearch & "*"") AND "

There appears to be no reference to cboPMID in your code. Line 16 above is testing the project manager ID field in your recordsource against the non-combo textbox contents – which will indeed have to be an ID, not the name of a person.

Could you explain further how cboPMID relates to the txtSearch contents, and what you expect to happen when a user selects a name from the combo?

Thanks

Stewart