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

Combo Box to Filter Records in a Continuous Form

P: 12
I am having trouble writing the code for a combo box as part of a string search criteria.

Scenario:
A form which displays all records in a table ([Visit Reports]) is filtered by several criteria fields. So far I have managed to succesfully filter certain columns in the table by using text fields, but I am unsure of how to filter other columns using combo boxes.

For instance, I have a "Status" column which has parameters; Open, Pending and Closed. I want users to be able to filter by selecting one of these in the combo box.

Below is the code I have used so far for the filter:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  3.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  4.                         we remove the trailing " AND " at the end.
  5.     '           2. The date range works like this: _
  6.                         Both dates      = only dates between (both inclusive. _
  7.                         Start date only = all dates from this one onwards; _
  8.                         End date only   = all dates up to (and including this one).
  9.     Dim strWhere As String                  'The criteria string.
  10.     Dim lngLen As Long                      'Length of the criteria string to append to.
  11.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  12.  
  13.     '***********************************************************************
  14.     'Look at each search box, and build up the criteria string from the non-blank ones.
  15.     '***********************************************************************
  16.     'Text field example. Use quotes around the value in the string. (Exact Value)
  17.  
  18.     'Hours
  19.     If Not IsNull(Me.txtFilterHours) Then
  20.         strWhere = strWhere & "([M1 Hours] = " & Me.txtFilterHours & ") AND "
  21.     End If
  22.  
  23.     'Customer Name
  24.     If Not IsNull(Me.txtFilterCustomerName) Then
  25.         strWhere = strWhere & "([Customer Name] Like ""*" & Me.txtFilterCustomerName & "*"") AND "
  26.     End If
  27.  
  28.     'Serial No
  29.     If Not IsNull(Me.txtFilterSerial) Then
  30.         strWhere = strWhere & "([M1 Serial] Like ""*" & Me.txtFilterSerial & "*"") AND "
  31.     End If
  32.  
  33.     'Issues
  34.     If Not IsNull(Me.txtFilterIssues) Then
  35.         strWhere = strWhere & "([Issues] Like ""*" & Me.txtFilterIssues & "*"") AND "
  36.     End If
  37.  
  38.      'Actions
  39.     If Not IsNull(Me.txtFilterActions) Then
  40.         strWhere = strWhere & "([Details] Like ""*" & Me.txtFilterActions & "*"") AND "
  41.     End If
  42.  
  43.     'Further Action
  44.     If Me.cboFilterFurtherAction = -1 Then
  45.         strWhere = strWhere & "([Further Action] = True) AND "
  46.     ElseIf Me.cboFilterFurtherAction = 0 Then
  47.         strWhere = strWhere & "([Further Action] = False) AND "
  48.     End If
  49.  
  50.  
  51.     'Date field example. Use the format string to add the # delimiters and get the right international format.
  52.  
  53.     If Not IsNull(Me.txtStartDate) Then
  54.         strWhere = strWhere & "([Visit Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
  55.     End If
  56.  
  57.     'Another date field example. Use "less than the next day" since this field has times as well as dates.
  58.  
  59.     If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
  60.         strWhere = strWhere & "([Visit Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
  61.     End If
  62.  
  63.  
  64.     '***********************************************************************
  65.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  66.     '***********************************************************************
  67.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  68.     lngLen = Len(strWhere) - 5
  69.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  70.         MsgBox "No criteria", vbInformation, "Nothing to do."
  71.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  72.         strWhere = Left$(strWhere, lngLen)
  73.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  74.         'Debug.Print strWhere
  75.  
  76.         'Finally, apply the string as the form's Filter.
  77.         Me.Filter = strWhere
  78.         Me.FilterOn = True
  79.     End If
  80. End Sub
  81.  
  82. Private Sub cmdReset_Click()
  83.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  84.     Dim ctl As Control
  85.  
  86.     'Clear all the controls in the Form Header section.
  87.     For Each ctl In Me.Section(acHeader).Controls
  88.         Select Case ctl.ControlType
  89.         Case acTextBox, acComboBox
  90.             ctl.value = Null
  91.         Case acCheckBox
  92.             ctl.value = False
  93.         End Select
  94.     Next
  95.  
  96.     'Remove the form's filter.
  97.     Me.FilterOn = False
  98. End Sub
The Row Source is "Status" and the combo box is called cboFilterStatus.

Many thanks in advance for your help.
Jan 10 '12 #1
Share this Question
Share on Google+
8 Replies


100+
P: 759
I don't understand the question.
I assume that you try but don't work.
Also I assume that you are in trouble because lines 44-48.

I think that the values for your combo box are not 0 or -1 (that sound like TRUE or FALSE).
So debug what value has your combo box before line 44.
For that insert before line 44 this code:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Me.cboFilterFurtherAction:
  2. Stop
Run the code and, when it stops, see in immediate window the value for your combo box.

Keep in mind that, for combo boxes (or list boxes), in Access, what you see is rarely equal with the control value. That because you can have hidden columns (especially the first column where is the key field).

Also take a look to .Columns property for combo boxes and list boxes (Access help).
Jan 11 '12 #2

P: 12
Thank you for your response Mihail.

Apologies, I didn't explain clearly enough. The cboFurtherAction code you mentioned works fine, that is just a Yes/No criteria unrelated to the question.

I'm trying to create a code (not written it into the code posted above yet) for a combo box where you select the item in the filter combo box, which then filters the respective recordset combo boxes in the continuous form, which fits in with the filter code I have posted above.

Keep in mind it must fit in with the code I used above, be a single selection combo box which filters the continuous form, and works in conjunction with the other criteria in the String.

Many thanks.
Jan 11 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
Have you looked through Example Filtering on a Form yet Gwyn? You may find answers there to your questions. If not, then let us know why your situation is different and we'll do what we can to help.
Jan 11 '12 #4

P: 12
I had a look at your link, but because I'm not too familiar with that code I couldn't poinpoint the relevant parts that might help.

If you look at lines 43-48 in my code, that is a Yes/No combo box filter criteria in my continuous form. But my problem is that I cannot work out the logic behind writing a similar code but for a combo box filter criteria which contains the RowSource "Status" (which contains; Open, Pending, Closed), so that I choose either "Open", "Pending" or "Closed" in the combo box, and click the cmdFilter button, and it will run the string filter you see in the code above.

Apologies for the long winded explanation, the problem seems like it should be so simple, yet I just cannot seem to work it out.

Many thanks.
Jan 11 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
The fundamentals you have in your code are pretty sound. Not exactly how I'd do it, but close enough for me to be impressed (with whomever provided the original code you were working from).

You might like to try :
Expand|Select|Wrap|Line Numbers
  1.     'Further Action
  2.     If Me.cboFilterFurtherAction > "" Then _
  3.         strWhere = strWhere & _
  4.                    "([Further Action] = '" & Me.cboFilterFurtherAction & "') AND "
These lines would replace lines #43 through #48 of your posted code.
Jan 11 '12 #6

P: 12
Thanks very much for your help. I get a Run Time Error '2001' appearing when I run the String as the filter (cmdFilter), with lines 77 and 78 highlighted as where the problem is. I have seen this happen a few times, with other parts of the code which I managed to fix.

Would you know what might be causing it?
Jan 11 '12 #7

Rabbit
Expert Mod 10K+
P: 12,366
If the filter on further action was working fine before NeoPa's change, then change it back. However, if neither worked, then I suspect the cboFilterFurtherAction combo box values are actually strings containing the values Yes and No.
Expand|Select|Wrap|Line Numbers
  1.     If Me.cboFilterFurtherAction = "Yes" Then 
  2.         strWhere = strWhere & "([Further Action] = True) AND " 
  3.     ElseIf Me.cboFilterFurtherAction = "No" Then 
  4.         strWhere = strWhere & "([Further Action] = False) AND " 
  5.     End If
As for the Status, you would take NeoPa's example and change the fields to fit. This assumes that what you see in the combo box is the value that is stored in the database.
Jan 11 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
Gwyn:
Would you know what might be causing it?
No. Principally because you didn't include the error message, but also because I don't understand why lines #77 & #78 are still in the same form after my post #6 in Search Criteria Error (which was posted before this thread was started).

You may find that checking through When Posting (VBA or SQL) Code (and particularly the linked How to Debug SQL String) will help you work that out for yourself anyway. If not, please post the information we'll require so that we can help you. Guessing is such an inefficient way of working.
Jan 11 '12 #9

Post your reply

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