Results 1 to 9 of 9
  1. #1
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111

    Check if a Control With Specified Name Exists on Form

    I'm wondering if it's possible to check if a control with a specified name exists on a form, perhaps something along the lines of a DLookup but for controls?

    Some Context..

    I've got multiple buttons on a form that belong to different groups.

    For example, lets say I'm giving someone the option of 1, 2, 3, 4, or 5 of an item (this is purely for the sake of an example, don't read too much into it)

    If they click the button for '2' that button's color darkens to give confirmation that it has been selected. Then, if they change their mind and click the button for '3', that button will darken, and the button for '2' will return to its original color.

    I'm trying to do this for multiple different groups of buttons without having to write code for each group individually. All the button groups are labeled similarly, such as "btnTE1", "btnTE2", "btnTE3" for one group, "btnCON1", "btnCON2" for another, so on and so forth.



    This is what I have so far.


    Code:
    Public Sub btnFilClr(btnPrefix As String)
    
    
    Dim clr1 As Long, clr2 As Long, btnName As String
    
    
    clr1 = RGB(122, 197, 205)
    clr2 = RGB(152, 245, 255)
    
    
    For i = 1 To 24
        
        btnName = btnPrefix & i
        
        If btnName = Screen.ActiveControl.Name Then
        
            Me.Controls(btnName).BackColor = clr1    ' --- This sets the control that was clicked to a darker color
            
        ElseIf *control with name exists on form* Then
            
    
            Me.Controls(btnName).BackColor = clr2    ' --- This sets all the other controls in that group to their original color
    
            
        End If
                
    Next
    Exit Sub
    End Sub
    btnPrefix would be something like "btnTE" or "btnCON", and i places the numbers in front of it. Also, i goes to 24 because that's the highest number of buttons in one group. The issue here is if a group doesn't have 24 buttons then a name is generated that won't exist on the form.

    There may be a simpler way of doing this, I have a tendency to overthink / over complicate things

    Hopefully I explained that well enough. If any additional information is needed please let me know. Any help is always appreciated!

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The easy route is to use the Tag value of a control - it can be set to anything your like.

    Have a look here for an excellent sample database https://www.access-programmers.co.uk...d.php?t=293439
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    I actually just got it to work using "On Error Resume Next" to skip past things that don't exist.

    Here's the code:

    Code:
    On Error Resume Next
    
    
    For i = 1 To 24
        
        btnName = btnPrefix & i
        
        If btnName = Screen.ActiveControl.Name Then
        
            Me.Controls(btnName).BackColor = clr1
            
        Else
            
            Me.Controls(btnName).BackColor = clr2
            
        End If
            
        
    Next
    Would using Tags work the same/be a better solution? I looked up what the Tag value is, and from what I read it doesn't look like that will accomplish what I need. According to what I read, you need to do something like "controlName.Tag", and if "controlName" doesn't exist it'll give the same error I was dealing with before. Or am I not understanding what you're suggesting?

  4. #4
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Ignoring error is not a good practice normally.

    If you look at the sample database it loops through all controls without referencing their names and checks if they have a tag property then what it is.
    You simply need to set the tag property on the controls you are interested in modifying.

    The logic can be demonstrated by simply making a button and adding this code to it's on click event

    Code:
        Dim Ctl              As Control
    
    
        For Each Ctl In Me.Controls
            
                If Len(Ctl.Tag & "") > 1 Then
                    Debug.Print Ctl.Name
                End If  
            
        Next Ctl
    Now in the debug immediate window (Press ctrl & G to see it) you will see how it will list any control with a tag value set.
    It's not rocket science to apply this to then check for a specific tag and then lock / enable / hide / change it's colour etc. etc.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    So this is what I came up with based on what you told me

    Code:
    Public Sub btnFilClr(btnPrefix As String)
    
    
    Dim btnName As String
    
    
    Dim ctrl As Control
    
    
    
    
    'Changes color of buttons when clicked
    For Each ctrl In Me.Controls
        
        If ctrl.Tag = btnPrefix Then
            
            btnName = ctrl.Name
            
                If btnName = Screen.ActiveControl.Name Then
                    
                    Me.Controls(btnName).BackColor = clr1
                    
                Else
                    
                    Me.Controls(btnName).BackColor = clr2
                
                End If
                    
                
        End If
        
        
    Next
    
    
    Exit Sub
    
    
    End Sub
    It works great, and I don't have to worry about the "On Error Resume Next" from my last code causing any problems. Thanks!

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm afraid I'm stepping away from my PC, but I think you've overcomplicated that...

    Let me look tomorrow.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I combined code from ItsRoland and Minty and came up with this demo.


    Good luck with your project.....
    Attached Files Attached Files

  8. #8
    ItsRoland is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    111
    Unfortunately I can't download attachments at work, so I can't see what you did. Can you post the code?

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For each and every button you want to set the BG color MUST have a value in the TAG property.
    For your example, I have 2 groups of buttons. The 1st group of 6 buttons have "btnTE" in the TAG property and the 2nd group of 7 buttons have "btnCON" in the TAG property.
    Although I think you should always take the time to give objects meaningful names, it doesn't matter what the button name is AS LONG AS the TAG property has a value (ie "btnTE" or "btnCON" or ... {don't include the quotes})

    So what you need to do is first set the BG color of the buttons (in the group defined by the TAG property) to the default BG color, and second, set the clicked BG color.

    The code is
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub btnCON1_Click()
        Call btnFilClr(Me.ActiveControl)
    End Sub
    
    Private Sub btnCON2_Click()
        Call btnFilClr(Me.ActiveControl)
    End Sub
    
    ' the rest of the button click events would follow
    
    
    ' the Sub that sets/resets the button BG colors
    Public Sub btnFilClr(theCtl As Control)
        Dim clr1 As Long, clr2 As Long
        Dim ctrl As Control
        Dim CurCtrl As Control
        Dim ctrltag As String
    
        clr1 = RGB(122, 197, 205)  'I would have used "DefaultBGClr" instead of "clr1"
        clr2 = RGB(152, 245, 255)  'I would have used "ClickedBGClr" instead of "clr2"
    
        Set CurCtrl = theCtl   'the button that called this sub
        ctrltag = CurCtrl.Tag   ' get the text from the tag property of the button clicked
    
        'reset the button BG color to the default (clr1)
        For Each ctrl In Me.Controls
            If ctrl.ControlType = 104 Then    ' 104 = button  --- only want to look at buttons
                If ctrltag = ctrl.Tag Then   ' check to see if the tag values match
                    Me.Controls(ctrl.Name).BackColor = clr1   'yep, so reset the BG color
                End If
            End If
        Next
    
        ' now set the BG for the button clicked
        Me.Controls(theCtl.Name).BackColor = clr2   
    
        Set ctrl = Nothing
        Set CurCtrl = Nothing
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-03-2017, 08:27 AM
  2. Check If Form Value Already Exists In Database
    By Richard_Marx in forum Modules
    Replies: 9
    Last Post: 08-15-2016, 09:32 AM
  3. Replies: 33
    Last Post: 09-16-2014, 12:47 PM
  4. Check if record exists, Check Number
    By burrina in forum Forms
    Replies: 9
    Last Post: 01-06-2013, 03:49 PM
  5. Replies: 3
    Last Post: 10-19-2012, 04:30 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