How do I apply a form filter to a combo box?

[Seth Schrock]

I have filtered a form as part of my record access control. On this form, I have a combo box that I use to navigate to different records on the form. I need this combo box to have the same filter as the form. I could just set the record source in the form's On_Load event using the WHERE clause of the query, but I would rather just set it so that the recordsource of the combo box would be the same as the form so that I wouldn't have to mess with the combo box. Is this possible?

[ADezii]

It is not the usual approach, but I feel as though it can be done.

1. If Active, the Form's Filter typically exists as:
   

   ((tblEmployees.Last="ADezii"))

2. You would then need to:
   1. Replace '(' and ')' with Zero Length Strings.
   2. Replace '"' with '.
   3. This will construct the WHERE Clause which you can then Append to a Base String representing the Row Source of the Combo Box.
3. The simplest scenario would then be:
   

   Dim strCriteria As String
    
   If Me.FilterOn Then     'Is the Filter Active?
     'Construct WHERE Clause for the Row Source
     strCriteria = Replace(Replace(Replace(Me.Filter, "(", ""), ")", ""), """", "'")
       Me![cboFilter].RowSource = "SELECT [Last] FROM tblEmployees WHERE " & strCriteria
   End If

4. P.S. - This simple scenario does actually work.

[Seth Schrock]

Okay, I have slightly modified your code to fit my row source. Here it is:

Dim strCriteria As String
 
If Me.FilterOn Then     'Is the Filter Active?
  'Construct WHERE Clause for the Row Source
  strCriteria = Replace(Replace(Replace(Me.Filter, "(", ""), ")", ""), """", "'")
    Me![cboRecordSearch].RowSource = "SELECT LoanID, Borrower & (', ' & LoanNumber) AS Combination " & _
                                     "FROM tblLoans WHERE " & strCriteria & " ORDER BY Borrower"
End If

My question now is do I need to create an Else in the If/Then statement to set the row source to the same query minus the WHERE clause? Some users don't have filters on the form (administrator, etc.) so the filter will be off.

[ADezii]

It is a simple matter to truncate the WHERE Clause if needed and apply it to the Form.

[Seth Schrock]

Okay. I got. Thanks ADezii!

[NeoPa]

I would humbly (except I never do humble!) suggest that the filter be transferred exactly as is. We all appreciate that Access is somewhat clumsy when producing SQL (Access QueryDefs Mis-save Subquery SQL), but in most cases it does work.

On the other hand, removing parentheses from a WHERE clause can be highly dangerous, as you may throw away some important ones. Likewise with the quotes. Changing the quotes (and I truly sympathise as Access uses the wrong characters by default - See Quotes (') and Double-Quotes (") - Where and When to use them) is not a safe thing to do, and is unnecessary. Consider a situation where Access has Martin O'Brien in the filter as "Martin O'Brien". Change the quotes to single (SQL standard) and you end up with 'Martin O'Brien', which will cause a failure. Likewise with sixteen inches written as "16""". Converted this would be '16''' (and without the tags - '16'''). It may look the same when displayed, but is a completely different string.

Access's version is messy, but I suggest you leave it as-is nevertheless.