How do I reactivate a button on an Access form after Filter by Form?

[Hagridore]

I may be about to surrender on this...I've asked in other forums and am not getting the answer I thought I'd get.

I'm creating an Access database that I ultimately want to compile into a stand-alone app. As part of this, I have a dashboard that opens when the database is opened, with various buttons to perform various tasks, most of which involve going to one part of the database or another. My primary table has 32 fields, and will ultimately have over a thousand records. Because any of the fields and any combination of them may need to be used for search criteria, I have set the button on the dashboard that allows one to search for a record and the one to edit a record to open their corresponding forms in Filter by Form mode. This works really well...the forms open just like I want, allowing users to type information into any control, and upon clicking Toggle Filters in the Home ribbon, exactly the subset of records being sought materialize as if by magic.

The challenge is this: I want to add a button to the form that runs the Toggle Filters process, so that I can make my app not have any of the Access ribbons in it. Unfortunately, when the form opens in Filter by Form mode, all buttons are greyed out and deactivated. On another help forum I posted this question and had someone say that it related to focus, but in spite of repeated questioning no one has been forthcoming there as to what commands are necessary to return the focus so that the button can be clicked. I need to know what the code is that I need to enter and where to enter it (i.e., in the Find Record button's On Click event or in the form's On Filter event).

Thank you in advance for any and all assistance.

[jforbes]

When using the FilterByForm functionality, it's pretty much all or nothing with the way Access expects you to use it. I'm not a big fan of FilterByForm. It looks good initially, but loosing the command button functionality is a big problem that can't be circumvented. Secondly, there is no way out of the FilterByForm with out using the Ribbon or Right-clicking the Form. Well, you can set focus on the Form and cause an error, but that would require code being started from someplace other than the Form that is being Filtered.

To address your frustration as to why you can't click on the button on your Form... When you click on the FilterByForm command on the Ribbon Access hides your current Form then creates a brand new Form based on the Form you were just on. This brand new Form is not a copy of your Form, but different Form altogether that impersonates the Original Form. Like a Pod Person =)

This Pod Person Form doesn't have any events of the Original Forms and the Buttons are just for show. They aren't just disabled, they do not have anything behind them. They are the Soulless eyes of the Pod People Form. I realize I'm taking this too far, but I'm having a lot of fun entertaining myself.

For what you are wanting to do, I think you have the hard decision to rely on the Ribbon (or custom Ribbon) or create a new Filtering Method.

[Hagridore]

I'm probably going to create a filter form. But your reference to focus makes me wonder. I have the form being opened in FilterByForm mode when I click a button on the main dashboard. This button (actually there're two with this purpose, each opening their own form, one in read only mode for finding and one in edit mode for, well, editing :-) has the code in it to open the form in that mode. Would this be where the focus code would be added? What is the code so I can check it out?

[jforbes]

I'm curious how you are opening the Form in FilterByForm Mode. Maybe there is something about how this is being accomplished that can we can work with. So, I'll trade you code.

OK, this code is a Hack. It is not good coding at all, but you asked for it... The idea is that when the Doppelganger Form gets focus and traps an error, it causes Access to Destroy the Doppleganger. I'm not sure if that is exactly what is happening, but it is how it acts.
Place this code on a the Form that you want to use the FilterByForm functionality. You'll also need to add a button to go along with it, if you don't already have one:

Private nFilterByForm As Boolean
Public Sub removeFilterByForm()
    On Error Resume Next
    If Screen.ActiveForm.Name = Me.Name And nFilterByForm Then DoCmd.RunCommand acCmdCloseWindow
End Sub
Private Sub cmdFilterByForm_Click()
    nFilterByForm = True
    DoCmd.RunCommand acCmdFilterByForm
End Sub
Private Sub Form_Current()
    nFilterByForm = False
End Sub

Then on another Form, like your switchboard, create a button with this code that turns off the Filtering (use your own Form Name):

Private Sub openTestFilterByForm_Click()
    Dim sFormName As String
    sFormName = "TestFilterByForm"
    If isLoaded(sFormName) Then
        Forms(sFormName).SetFocus
        Forms(sFormName).removeFilterByForm
    Else
        DoCmd.OpenForm sFormName
    End If
End Sub

This is a function that is used to see if a Form is loaded that I use often, I included it because it was easier for me that way:

Function isLoaded(ByRef sFormName As String) As Boolean
    ' Determines if a Form is loaded
    Dim i As Integer
 
    isLoaded = False
    For i = 0 To Forms.Count - 1
        If Forms(i).FormName = sFormName Then
            isLoaded = True
            Exit Function
        End If
    Next
End Function