By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Problem filtering a combobox with textbox input

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.  
  3.     Dim strWhere As String                  'The criteria string.
  4.     Dim lngLen As Long                      'Length of the criteria string to append to.
  5.  
  6.     If cboSearchCriteria = "TR #" Then
  7.         If Not IsNull(Me.txtSearch) Then
  8.             strWhere = strWhere & "([TR#] Like ""*" & Me.txtSearch & "*"") AND "
  9.         End If
  10.     ElseIf cboSearchCriteria = "TR Description" Then
  11.         If Not IsNull(Me.txtSearch) Then
  12.             strWhere = strWhere & "([ccDescription] Like ""*" & Me.txtSearch & "*"") AND "
  13.         End If
  14.     ElseIf cboSearchCriteria = "Project Manager" Then
  15.         If Not IsNull(Me.txtSearch) Then
  16.             strWhere = strWhere & "([ccProjManID] Like ""*" & Me.txtSearch & "*"") AND "
  17.         End If
  18.  
  19.        lngLen = Len(strWhere) - 5
  20.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  21.         MsgBox "No criteria", vbInformation, "Nothing to do."
  22.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  23.         strWhere = Left$(strWhere, lngLen)
  24.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  25.         'Debug.Print strWhere
  26.  
  27.         'Finally, apply the string as the form's Filter.
  28.         Me.Filter = strWhere
  29.         Me.FilterOn = True
  30.     End If
  31. End Sub
  32.  
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
Mar 14 '08 #1
Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 15 '08 #2

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 17 '08 #3

P: 4
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
Mar 17 '08 #4

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 17 '08 #5

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 18 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.