Filter a continuous form

Question

Hi

I would like to filter a continuous when the user clicks on a field [LeadCaseOfficer] using the value of the field I clicked on.

So if the continuous form had three records, and the LeadCaseOfficer's were John and Jane, with John having two cases and Jane having one, when I click on the John cell, it filters the continuous form to only show records where the LeadCaseOfficer is John.

Does anyone know the VBA or Macro builder process? I have tried many things and failed.

Thanks in advanced.

John

---

JG

Answer 1

It appears from your screenshot that LeadCaseOfficer is a combo box.  That makes it trickier.  You'd think you could use the Click event for this, but you can't, because combo boxes only raise that event when the value of the combo box is actually changed -- they pervert the normal usage of the Click event.  You might use the MouseDown or MouseUp event, but they also fire when you use the mouse to change the value of the combo box by dropping down the list, so that would probably cause filtering based on either of those events to occur when you don't want it to. 

Can you use the DblClick event instead?  Then there'd be no confusion with mouse actions used to select a value from the dropdown list.  VBA code would look like:

Private Sub LeadCaseOfficer_DblClick(Cancel As Integer)

    If Not IsNull(Me.LeadCaseOfficer) Then

        ' Use the following if LeadCaseOfficer is a number field:
        Me.Filter = "LeadCaseOfficer = " & Me.LeadCaseOfficer
        
        ' Use the following if LeadCaseOfficer is a text field:
        'Me.Filter = "LeadCaseOfficer = """ & Me.LeadCaseOfficer & """"
        
        Me.FilterOn = True
        
    End If

End Sub

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Hi Dirk,

Superb response. Thanks for being so thorough and clear. I added the filter code to the double click event and it worked perfectly. I also changed the combo box to a text box on that specific search form and then as you indicated the on click event worked (this is probably the solution I will go with, as I will make the text box formatted as a hyperlink and the cursor will turn into a hand on mouse over and the click event will fire on a single click).

That was a tricky one I would have never worked out why the event was not firing!

I (and the end users) thank you for your help.

Kind regards

John

---

JG

Answer 3

I also changed the combo box to a text box on that specific search form and then as you indicated the on click event worked (this is probably the solution I will go with, as I will make the text box formatted as a hyperlink and the cursor will turn into a hand on mouse over and the click event will fire on a single click).

That sounds like an even better solution.

Thanks for the kind words.  You're welcome.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html