none
Turn off filter in VBA code RRS feed

  • Question

  • A list of clients from the Client table has the ClientID made into a link so instead of searching for the name, the user can scroll, find the name and click the link with contains the code:

    Const cstrForm As String = "frmClient"
        DoCmd.OpenForm cstrForm, WhereCondition:="[ClientID]=" & Me.ClientID

    After finding the client this way, the "Filtered" light and message come on at the very bottom of  the app, so that if I click the arrow to get to the next client, it just goes to "New". I added:

    Me.FilterOn = False

    ....after the DoCmd code, but I still have to manually click "Filtered" to get it to go away and then click the arrow to get to the next record. Any suggestions?



    • Edited by 985Hunter Friday, October 21, 2016 10:36 PM
    Friday, October 21, 2016 10:35 PM

Answers

  • Initially it was just a field containing the ClientID in continuous form. I read on a webpage that you can make it a link by changing the property "Is Hyperlink" to yes and it gave the code to go with it. Even when I removed the hyperlink so the blue underline goes away, the old code still takes it to the record. It just has that pesky "Filtered" lit up in orange at the bottom.

    Yes it is a bound field to Client ID. Maybe that's what's wrong?

    No, I expect your ClientID field to be bound to the ClientID field in the table.  Let me check one thing:  is the underlying field named "ClientID", as your code implies, or is it named "Client ID", as you just stated?  Is ClientID a number field (including Autonumber) or a text field?

    I just tested again by building a continuous form based on tblClients (primary key ClientID, of type Autonumber).  On that form, I set the IsHyperlink property of the ClientID control to Yes, and gave it this code:

    Private Sub ClientID_Click()
    
        Const cstrForm As String = "frmClient"
    
        DoCmd.OpenForm cstrForm
            
        If Not IsNull(Me.ClientID) Then
        
            With Forms(cstrForm).Recordset
                .FindFirst "[ClientID]=" & Me.ClientID
                If .NoMatch Then
                   MsgBox "ClientID '" & Me.ClientID & "' was not found."
                End If
            End With
        
        End If
    
    End Sub
    

    I also have a form named "frmClient", bound to the same table.

    When I open the continuous form, the ClientID control appears as a hyperlink, and when I click it, frmClient opens, properly positioned to the record with the ClientID I clicked.

    So it definitely works for me, even with a continuous form that is set up like yours. I'm still finding it hard to imagine how opening the form using DoCmd.OpenForm with the WhereCondition argument can work for you, while the code above does not.

    Could you possibly post for download, or send to me via e-mail, a copy of your database?  You can get my e-mail address from my forum profile or from my website, which is listed in my signature.  It doesn't have to be the full database; a cut-down copy will suffice, with just the elements necessary to demonstrate this problem.


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

    • Marked as answer by 985Hunter Monday, October 24, 2016 5:55 PM
    Sunday, October 23, 2016 6:30 PM

All replies

  • As you've discovered, if you open the form with the WhereCondition set to filter it, that's what happens.  Do you want to open the form to display all records (that is, unfiltered), but positioned to a specific record?  One way to do that would be to use code like this to open and position the form:

    Const cstrForm As String = "frmClient"
    
    ' Open the form unfiltered.
    DoCmd.OpenForm cstrForm
    
    ' Position the form to the desired ClientID
    Forms(cstrForm).Recordset.FindFirst "[ClientID]=" & Me.ClientID


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

    Friday, October 21, 2016 10:44 PM
  • I do want the form with the client info to open when I click the link on the ClientID field. I changed the code as described, but now it will not open the form with the correct client I selected. The other version worked, opened the right Client record, but the filter would not turn off so I could click to the next sequential record at the bottom.

    Other ideas?

    Friday, October 21, 2016 10:54 PM
  • I do want the form with the client info to open when I click the link on the ClientID field. I changed the code as described, but now it will not open the form with the correct client I selected. The other version worked, opened the right Client record, but the filter would not turn off so I could click to the next sequential record at the bottom.

    Other ideas?

    It works for me, so there has to be something else going on.  Please post the exact code you are using while trying to do it the way I suggested.

    It occurs to me belatedly to ask:  have you verified that the client ID on the calling form ("Me.ClientID" in the code) contains a client ID value that is in the recordset of frmClient?


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


    • Edited by Dirk Goldgar Friday, October 21, 2016 11:29 PM late thought
    Friday, October 21, 2016 11:27 PM
  • Here is all code for this particular button. The original code is commented out.  The link button is in a subform (continuous) if that makes any difference.

    Private Sub ClientID_Click()
    Const cstrForm As String = "frmClient"
        DoCmd.OpenForm cstrForm
        Forms(cstrForm).Recordset.FindFirst "[ClientID]=" & Me.ClientID
       
    'Const cstrForm As String = "frmClient"
    '     DoCmd.OpenForm cstrForm, WhereCondition:="[ClientID]=" & Me.ClientID


    End Sub

    Friday, October 21, 2016 11:38 PM
  • Here is all code for this particular button. The original code is commented out.  The link button is in a subform (continuous) if that makes any difference.

    Private Sub ClientID_Click()
    Const cstrForm As String = "frmClient"
        DoCmd.OpenForm cstrForm
        Forms(cstrForm).Recordset.FindFirst "[ClientID]=" & Me.ClientID
       
    'Const cstrForm As String = "frmClient"
    '     DoCmd.OpenForm cstrForm, WhereCondition:="[ClientID]=" & Me.ClientID


    End Sub

    I'm puzzled. What kind of control (on subform) is ClientID? You're using its Click event, and you referred to a "link button", but a command button wouldn't have a value to use in a FindFirst or WhereCondition argument.

    Is ClientID a combo box?  If so, it would have both a value and a useful Click event, so maybe that's it.  If that's the case, is the bound column of the combo box the actual ClientID value?

    I can't think of a circumstance where your original code to open the form with WhereCondition would work, and my suggested code to use FindFirst would not.  Unless frmClient were bound to an ADO recordset, instead of the normal DAO.  This code isn't in an ADP, is it?  If so, that would make a difference.

    Note: The code I posted won't let you know if the ID you are looking for doesn't exist.  If you want, you could modify it like this to display a message if that happens:

        DoCmd.OpenForm cstrForm
    
        With Forms(cstrForm).Recordset
            .FindFirst "[ClientID]=" & Me.ClientID
            If .NoMatch Then
               MsgBox "ClientID '" & Me.ClientID & "' was not found."
            End If
       End With
     


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

    Friday, October 21, 2016 11:49 PM
  • Initially it was just a field containing the ClientID in continuous form. I read on a webpage that you can make it a link by changing the property "Is Hyperlink" to yes and it gave the code to go with it. Even when I removed the hyperlink so the blue underline goes away, the old code still takes it to the record. It just has that pesky "Filtered" lit up in orange at the bottom.

    Yes it is a bound field to Client ID. Maybe that's what's wrong?

    • Edited by 985Hunter Saturday, October 22, 2016 12:07 AM
    Friday, October 21, 2016 11:53 PM
  • You might like to take a look at FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes examples of how to go to a specific record, as you want to do, and of filtering a form to one or more matching records, which is not what you want to do.

    Ken Sheridan, Stafford, England

    Saturday, October 22, 2016 12:25 PM
  • Initially it was just a field containing the ClientID in continuous form. I read on a webpage that you can make it a link by changing the property "Is Hyperlink" to yes and it gave the code to go with it. Even when I removed the hyperlink so the blue underline goes away, the old code still takes it to the record. It just has that pesky "Filtered" lit up in orange at the bottom.

    Yes it is a bound field to Client ID. Maybe that's what's wrong?

    No, I expect your ClientID field to be bound to the ClientID field in the table.  Let me check one thing:  is the underlying field named "ClientID", as your code implies, or is it named "Client ID", as you just stated?  Is ClientID a number field (including Autonumber) or a text field?

    I just tested again by building a continuous form based on tblClients (primary key ClientID, of type Autonumber).  On that form, I set the IsHyperlink property of the ClientID control to Yes, and gave it this code:

    Private Sub ClientID_Click()
    
        Const cstrForm As String = "frmClient"
    
        DoCmd.OpenForm cstrForm
            
        If Not IsNull(Me.ClientID) Then
        
            With Forms(cstrForm).Recordset
                .FindFirst "[ClientID]=" & Me.ClientID
                If .NoMatch Then
                   MsgBox "ClientID '" & Me.ClientID & "' was not found."
                End If
            End With
        
        End If
    
    End Sub
    

    I also have a form named "frmClient", bound to the same table.

    When I open the continuous form, the ClientID control appears as a hyperlink, and when I click it, frmClient opens, properly positioned to the record with the ClientID I clicked.

    So it definitely works for me, even with a continuous form that is set up like yours. I'm still finding it hard to imagine how opening the form using DoCmd.OpenForm with the WhereCondition argument can work for you, while the code above does not.

    Could you possibly post for download, or send to me via e-mail, a copy of your database?  You can get my e-mail address from my forum profile or from my website, which is listed in my signature.  It doesn't have to be the full database; a cut-down copy will suffice, with just the elements necessary to demonstrate this problem.


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

    • Marked as answer by 985Hunter Monday, October 24, 2016 5:55 PM
    Sunday, October 23, 2016 6:30 PM
  • Thank you Ken. I do want to explore your database and will do so soon. I need some extra time to really study it all.
    Monday, October 24, 2016 3:39 PM
  • Your code works! When I tried the code you suggested the first couple of times, I put both of these lines together:

    With Forms(cstrForm).Recordset.FindFirst "[ClientID]=" & Me.ClientID

    That led to compile errors. But I fixed it, according to how you had it down. I didn't realize that the where clause filtered this way based on the source where I found it. Thank you for not giving up on me!

    Monday, October 24, 2016 5:54 PM
  • You're welcome.  I'm glad you got it working.

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

    Monday, October 24, 2016 6:10 PM
  • FYI... I figured out that in two cases I couldn't get your code to work because they are combo box records. With the error trap you showed me, I can see clearly that it just can't go to records in the same way as other forms. So I will have to figure that out later. A thousand thanks to you!
    Monday, October 24, 2016 10:01 PM
  • FYI... I figured out that in two cases I couldn't get your code to work because they are combo box records. With the error trap you showed me, I can see clearly that it just can't go to records in the same way as other forms. So I will have to figure that out later. A thousand thanks to you!
    I'm not sure what you mean by "combo box records".  I can guess that maybe your combo boxes were displaying one value but bound to another, but if you want to pursue this, please explain in more detail how the forms and relevant controls are set up, and what you saw when you ran the code.

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

    Monday, October 24, 2016 10:15 PM
  • After your reply, I went back and tried them again. They worked this time, but I had made no other changes. They were giving the error message you set up such as "ItemID 6 not found". These two forms showed data that was supplied to combo boxes in other forms. I have no explanation why it might be working now. Btw... I was really impressed with your work on that hospital and centrex records. Automating that was no easy task.
    Monday, October 24, 2016 10:54 PM
  • After your reply, I went back and tried them again. They worked this time, but I had made no other changes. They were giving the error message you set up such as "ItemID 6 not found". These two forms showed data that was supplied to combo boxes in other forms. I have no explanation why it might be working now.

    Hard to figure.  Something changed, but without a before and after snapshot, I can't guess what.  At least it's working!

    Btw... I was really impressed with your work on that hospital and centrex records. Automating that was no easy task.

    You must have visited my website.  Thanks.


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

    Tuesday, October 25, 2016 4:06 PM