Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Search for a control in form

    So I have a switchboard form that has many controls including several command buttons. There are so many that I have hard a time finding the one I need sometimes, so I decided to create a search box hoping to highlight the command button in need. I created a text box (Name = txtSearch) in the form. I have a After Update event routine below, but it does not quite work. This line is not being executed
    Code:
    If ctl.Caption = " & " Like "'*" & strSeachText & "*'" Then
    . Can someone help me with the syntax?

    For every command button, I do name them with prefix of "cmd....", thus the BackColor should change only for command buttons.



    Code:
    Private Sub txtSearch_AfterUpdate()    Dim strSeachText As String
        Dim ctl As Control
        Dim strCommandName As String
        strSeachText = Nz(Me.txtSearch)
        For Each ctl In Me.Detail.Controls
        strCommandName = ctl.Name
        If Left(strCommandName, 3) = "cmd" Then
    '        Debug.Print ctl.Caption
            If ctl.Caption = " & " Like "'*" & strSeachText & "*'" Then
                ctl.BackColor = vbYellow
            End If
        End If
        Next ctl
        Set ctl = Nothing
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Why would you search controls?
    you should be searching data.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It is an odd thing to need, but that line doesn't make sense. Try:

    If ctl.Caption Like "'*" & strSeachText & "*'" Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are so many yet you know all their names? I ask that one because if you have cmdCustDetail and cmdCustOrder and you search on Cust which one do you want to highlight?
    Maybe it's time to split things up so they appear on one screen? Or organize them into banks/columns while controlling visibility? How many are we talking about here?
    Maybe use a combo with filter as you type feature, then pick it from the list? Kind of like the controls combo you see in design view.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or perhaps in design view, open the properties window and select the control you want from the dropdown - it will then be highlighted

  6. #6
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by pbaldy View Post
    It is an odd thing to need, but that line doesn't make sense. Try:

    If ctl.Caption Like "'*" & strSeachText & "*'" Then
    This seems to be the right syntax, but it still doesn't work for me for some reason.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here? You would also need an Else setting back to default. I'm not at a computer, can you manually set the back color of a button?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by pbaldy View Post
    Can you attach the db here? You would also need an Else setting back to default. I'm not at a computer, can you manually set the back color of a button?
    I believe you can. I couldn't get the line to execute, so I'm not for certain. I will have to add some more lines for it to go back to the default color. Here's the condense db.
    Test.accdbTest.accdb

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    remove the single quotes. You're passing a variable of type string so no need to quote it. If you were passing a string, then yes, you would need them.

    strcaption Like "*" & strSeachText & "*"

    EDIT:
    BTW, I think this was mentioned, but you have no way to restore the original color, save perhaps by closing the form. Every subsequent different search will cause more controls to go yellow (or whatever color you're sticking with). Suggest you do something different, such as storing the original color in the control Tag property and set them all to their original color in the same pass - e.g. if they are not Tag color, make them tag color.

    You also have a duplicate keyboard shortcut on 2 left red buttons.

  10. #10
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Micron View Post
    remove the single quotes. You're passing a variable of type string so no need to quote it. If you were passing a string, then yes, you would need them.

    strcaption Like "*" & strSeachText & "*"

    EDIT:
    BTW, I think this was mentioned, but you have no way to restore the original color, save perhaps by closing the form. Every subsequent different search will cause more controls to go yellow (or whatever color you're sticking with). Suggest you do something different, such as storing the original color in the control Tag property and set them all to their original color in the same pass - e.g. if they are not Tag color, make them tag color.

    You also have a duplicate keyboard shortcut on 2 left red buttons.
    Ha, removing single quotes did the trick. I'll add a few lines to store the original backcolor then restore it right afterwards. I'll share the codes when done. Thanks, Micron.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Often when someone is doing something I've never tried before (not that I always see myself ever using the technique in question) I play with it and have done so in this case. We can compare notes/methods when you're done if you like.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Had some free time, so I played around with this dB.
    Added code for the "Clear" button and for resetting the buttons to their default color using the Tag property.
    Attached Files Attached Files

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Interesting. I thought you had to convert the color number to hex but I don't see that you did so. I passed the form, field to sort by and the sort order to a public sub so that it could be used by any form. The gauntlet has been thrown down!

    The sort is based on a form module level variable:
    Option Compare Database
    Option Explicit
    Dim strSort As String

    In control event (AfterUpdate or whatever)
    Code:
    Dim ctl As Control
    
    On Error GoTo errHandler
        
    If Not IsNullEmpty(Me.txtSearch) Then
     For Each ctl In Me.Detail.Controls
       If ctl.ControlType = acCommandButton And ctl.Tag <> "" Then
         ctl.BackColor = GetRGB(ctl.Tag)
         If ctl.Caption Like "*" & Me.txtSearch & "*" Then ctl.BackColor = vbYellow
      End If
     Next ctl
    End If
    
    exitHere:
    On Error Resume Next 'possible to raise error before any object gets assigned to ctl, which in itself would raise an error
    Set ctl = Nothing
    Exit Sub
    
    errHandler:
    MsgBox "Error No. " & Err.Number & ": " & Err.Description
    Resume exitHere
    standard module:
    Code:
    Function IsNullEmpty(ctl As Control) As Boolean
      IsNullEmpty = Nz(ctl, "") = ""
        
    End Function
    
    Function GetRGB(strVal As String) As Long
    'code adapted from https://access-programmers.co.uk/for...d.php?t=267808
    Dim strR As String, strG As String, strB As String
    
    If Left(strVal, 1) = "#" Then strVal = Right(strVal, Len(strVal) - 1)
    strR = Left(strVal, 2)
    strG = Mid(strVal, 3, 2)
    strB = Right(strVal, 2)
    GetRGB = CLng("&H" & strR & strG & strB)
    
    End Function
    I took the liberty of basing the search on control type rather than cmd being part of a name string because I use cmd for all command buttons anyway, plus wanted to show OP the use of the control type property. Also, the IsNullEmpty function may prove useful...
    I never bothered to deal with the Clear button, so I think you win.
    Last edited by Micron; 12-10-2018 at 10:37 PM. Reason: posted code from wrong db

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a version even more convenient, a small modification of Steve's submission. It updates the buttons after each keystroke in the search box.
    TestMod-davegri-v01.zip
    Last edited by davegri; 12-10-2018 at 10:44 PM. Reason: credit

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by davegri View Post
    Here's a version even more convenient, a small modification of Steve's submission. It updates the buttons after each keystroke in the search box.
    TestMod-davegri-v01.zip
    You're right, it's late, as you discovered. The email is a bit different than your current post, yes?


    Hmmm, you don't mind all that flashing when you type? Users better not have epilepsy!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-09-2018, 11:11 PM
  2. Replies: 3
    Last Post: 03-18-2016, 05:02 PM
  3. Replies: 4
    Last Post: 02-18-2016, 12:06 PM
  4. Replies: 2
    Last Post: 01-16-2015, 05:56 AM
  5. Wildcard search within ComboBox to control RowSource
    By CaptainKen in forum Programming
    Replies: 22
    Last Post: 05-16-2012, 02:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums