Results 1 to 13 of 13
  1. #1
    stainl77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    5

    Can macros be sent the name of the control name from an event


    Wasn't sure whether this was really a macro, vba or form question. However here goes.

    I have several text boxes that, on click, currently call one macro to run vba code. In the VBA code, I wanted to use the name of the control, that prompted the on click event, in the logic. I tried Active Screen control but that is not giving me the correct control. I know there is an option to create a Macro for each control (25 of them) and run the code with a variable passed in that identifies the specific OnClick control event. But 25 macros seems excessive. Is there any way that one macro can identify the OnClick control event that has triggered it, so that I can either directly send these details to my run code with one control name variable or, at worst, place logic in the macro for 25 different controls?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a look at code that utilises With Events such as this;

    https://codekabinett.com/rdumps.php?...finite-sources
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I tried Active Screen control but that is not giving me the correct control.
    Could depend on what event you used when trying to get it. If an After Update event, this is usually triggered by moving off of the control (or record) but you don't say what the result was nor if you used Screen.ActiveControl.Name (going from memory there). If you've already moved off, then of course you'll get the wrong name. That may not be the case with a continuous or datasheet form. As long as you use the right event and only want the control name I would have thought that would work. However, it won't give you the name of the event if that's what you're trying to pass.

    The article link looks to me as if it's about handling events whereas your post indicates you want the control name.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you are using a macro to call some vba code, is it possible to use a standard function with some parameters??
    I think we need more info/details or some code to review to offer more focused responses.
    Perhaps the Tag property of each control could be of some use?

  5. #5
    stainl77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    5
    Quote Originally Posted by stainl77 View Post
    Wasn't sure whether this was really a macro, vba or form question. However here goes.

    I have several text boxes that, on click, currently call one macro to run vba code. In the VBA code, I wanted to use the name of the control, that prompted the on click event, in the logic. I tried Active Screen control but that is not giving me the correct control. I know there is an option to create a Macro for each control (25 of them) and run the code with a variable passed in that identifies the specific OnClick control event. But 25 macros seems excessive. Is there any way that one macro can identify the OnClick control event that has triggered it, so that I can either directly send these details to my run code with one control name variable or, at worst, place logic in the macro for 25 different controls?


    I will give more details as requested.


    I have 25 comboboxes called RepColumnsCombo1 through to RepColumnsCombo25.


    I want actions to occur immediately that an item is clicked in a combobox and so have put an action in the OnClick event. I really don't want to work with an AfterUpdate as there is no reason to tab away from the ComboBox at this point. Currently, the OnClick calls a macro called ComboItemSelected which in turn currently runs code called RepComboSelected. I realise that RepComboSelected could be an OnClick within the form class module and the macro could be bypassed entirely.


    However, the code itself accesses the Screen.ActiveControl property but that is not reflecting that the click is in the combobox, so I don't think I can rely on that.


    I have attached screen prints plus the first bit of code in the VBA function. If the code could get the combobox name, my logic can carry out actions that relate to the screen design and other fields. However, at the moment, because I am not receiving info on which combobox initiated the event, it does not work.

    Screen populated showing combo boxes under heading Report Columns:
    Click image for larger version. 

Name:	Screenshot 06-24-2021 19.42.00.png 
Views:	16 
Size:	27.1 KB 
ID:	45555

    Screen design showing properties of one box, RepColumnsCombo1:
    Click image for larger version. 

Name:	Screenshot 06-24-2021 19.43.44.png 
Views:	17 
Size:	66.4 KB 
ID:	45556

    Called macro:
    Click image for larger version. 

Name:	Screenshot 06-24-2021 19.44.53.png 
Views:	17 
Size:	5.5 KB 
ID:	45557



    Called code:

    Code:
    Public Function RepComboSelected()
    
    
        Dim ctlCurrentControl As Control
     
        Set ctlCurrentControl = Screen.ActiveControl
    
    
        Dim LockCheck As String
        LockCheck = "LockCheck" & Replace(ctlCurrentControl.Name, "RepColumnsCombo", "")
        Form_ColumnsToReport.ColumnsToReport_subform.Form.Controls(LockCheck).Enabled = True
        
        LockChanged
        
        If InStr(ReportDateCombinations, ctlCurrentControl.value) > 0 Then
            DoCmd.OpenForm "DateFormatForm"
            Form_DateFormatForm.SetFocus
        End If
            
    End Function

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can get the combobox names with code along this set up: Where Me represents your form.

    Code:
    .....
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Then
           '''Your logic goes here based on the name of the combo control"""
        End If
    Next
    ....
    Last edited by orange; 06-24-2021 at 03:25 PM.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you don't mind doing the work, change function call to ComboItemSelected(putNameHere) and the function to ComboItemSelected(strName As String). That is 25 function call edits, though. If there is an easier way, I don't know of it - yet.

    EDIT - aside from ActiveControl, which I'm not seeing why it wouldn't be reliable given the event you're using for the combo, or using tag property for each combo. One thing I would do is name a group of controls like that to
    cmbRepCols1
    What you have is just too much for my lazy fingers.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    stainl77 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2021
    Posts
    5
    Quote Originally Posted by Micron View Post
    If you don't mind doing the work, change function call to ComboItemSelected(putNameHere) and the function to ComboItemSelected(strName As String). That is 25 function call edits, though. If there is an easier way, I don't know of it - yet.

    That's what I was going to do, if there wasn't a shorter method. I don't know why the ActiveControl command is not returning the ComboBox name, even though I have not tabbed off the box. However, it is what it is so I will pass a unique string into the function.

    Thanks for everyone's help

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Post your actual attempt?
    I'm not understanding why a macro needs to be involved here. Why have a mix of code and a macro?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can have the function identify which combobox is invoking it like this, directly from the form event. No macro needed:

    Code:
    Function RepComboSelected(arg As Long)
        Select Case arg
            Case 1
                MsgBox "RepColumnCombo1 was clicked"
            Case 2
                MsgBox "RepColumnCombo2 was clicked"
    '        .
    '        .
            Case 25
                MsgBox "RepColumnCombo25 was clicked"
            Case Else
                MsgBox "unknown combobox ref???"
        End Select
    End Function
    And the form...

    Attachment 45559

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Umm, that was suggested in post 7, albeit to use the control name instead of a number?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by Micron View Post
    Umm, that was suggested in post 7, albeit to use the control name instead of a number?
    Aye, so it was. I should pay more attention to other's details.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Me too, sometimes. When I do it, I think "Great minds think alike."
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 8
    Last Post: 01-10-2021, 04:58 AM
  2. Replies: 6
    Last Post: 06-04-2018, 06:46 AM
  3. Tab Control Event
    By Paul H in forum Forms
    Replies: 4
    Last Post: 07-31-2017, 11:47 AM
  4. Database event macros
    By SA10014321 in forum Access
    Replies: 4
    Last Post: 12-07-2015, 02:49 PM
  5. Capture an “Enable Macros” warning event
    By LindaRuble in forum Programming
    Replies: 1
    Last Post: 03-19-2013, 10:22 AM

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