• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 104
  • Last Modified:

how to call a sub using a string variable?

Hi Experts,

I would like to call a sub using a variable, tried the below but they only seem to work for functions and not for sub's.

   
varMySub="CheckBackgroundCheckReqYN_AfterUpdate()"
 'Application.Run varMySub
    CallByName , varMySub

Open in new window

0
bfuchs
Asked:
bfuchs
  • 7
  • 5
  • 3
  • +3
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
first, you need to make the sub public
PUBLIC Sub CheckBackgroundCheckReqYN_AfterUpdate()


the call will be different depending on where you are calling the sub

where do you want to call the sub?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Seriously ... why not just change it to a Function. There is zero advantage of a Sub in today's world.
Done ! Boom!
0
 
bfuchsAuthor Commented:
@Ray,@ Joe

As you can see from the name, this is the build in sub that fires when changing a control's value, how could I change it to public/function?

where do you want to call the sub?
I'm calling it within the form itself.

Thanks,
Ben
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
you use  a function if you want to  return a value as a result of executing the function.

why do you need to call the sub "CheckBackgroundCheckReqYN_AfterUpdate()"
outside of the AfterUpdate event of the checkbox?
0
 
bfuchsAuthor Commented:
Hi Rey,

You remember a while back when I needed to display an X instead of blank for a check box, you helped me do the label stuff..now I'm simply following that route,
see attached.

What happens if I have code to be executed when the check box gets updated..I have to rely on the after update event.

Any alternative?

Thanks,
Ben
untitled.png
0
 
Gustav BrockCIOCommented:
You can do it very simple:
   
    varMySub = "CheckBackgroundCheckReqYN_AfterUpdate"

    Select Case varMySub
        Case = "CheckBackgroundCheckReqYN_AfterUpdate"
            Application.Run CheckBackgroundCheckReqYN_AfterUpdate
            CallByName, CheckBackgroundCheckReqYN_AfterUpdate
        Case Else
            ' Other stuff.
    End Select

Open in new window

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<What happens if I have code to be executed when the check box gets updated..I have to rely on the after update event.>>

  You need do it the way gustav outlined then.   You can call a function easily enough by string certainly (eval(strFunctionname) works as well), but not a sub.   This is the way macro's do it, which is why they are limited to calling functions.

The other approach would be to use a class, then define all your checkboxes as that class.   You could then sink the event and execute one sub.  

I'd probably do it gustav's way though.

Jim.
0
 
PatHartmanCommented:
Both Subs and Functions can take parameters but only a Function will return a value.  The reason that this Sub won't take a parameter is because it is not defined by you.  The event subs for controls and forms/reports are defined by Access and Access doesn't expect them to take parameters.

If you want to standardize the code and use it from multiple places, extract it from the AfterUpdate sub and create a separate sub in a standard class module.  Define the sub to take a form argument.  Call the sub from any form.

Public Sub MySub(frm as Form)
   Msgbox "This is the name of the form that called me - " & frm.Name
End Sub

Call MySub(Me)

"Me" is the reference to the current form
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
RE "how could I change it to public/function?"

Sorry I missed this was a built in Sub ...
0
 
bfuchsAuthor Commented:
Hi Experts,

Excuse me for the delay, just arrived at work..

@Gustav, Jim & Pat,

I think would be better if I first describe where I'm coming from (https://www.experts-exchange.com/questions/28946940/How-to-display-X-in-a-check-box-control.html) & what I'm trying to accomplish..

As you can see from above link and on previous attachment, I'm using a label instead of a checkbox, and by clicking on the label the checkbox gets updated.

Now since most if not all check boxes have code associated with the update, I need to execute it along with the controls update, and that all by clicking on the label.

Up till now everything is simple..

Here comes the issue, since there will be many such checkboxes/labels, I would like to do something like general code that according to which label was clicked the code would update a certain check box and fire the after update of that check box.

After that all I have to do in form design is to assign the name of the function performing the above to the labels click event.

(I had seen similar functionality in forms that are meant to act like a calendar, see attached)

Let me know if thats clear enough.

Thanks,
Ben
untitled.png
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Here comes the issue, since there will be many such checkboxes/labels, I would like to do something like general code that according to which label was clicked the code would update a certain check box and fire the after update of that check box.>>

 If that's the case, just duplicate what you see in the calendar.

 The issue we were dealing with above is when you want to call a different procedure for each control.

 But if they will all work the same, then you can call a common sub/function and pass an argument with the control name and then work with that.

Jim.
0
 
bfuchsAuthor Commented:
Hi Jim,

The issue we were dealing with above is when you want to call a different procedure for each control
Well I think thats the case here.
below is the code I'm trying to standardize,
ApplyLabelCheck CheckPriority1CompleteYN, , Me.Form, False
    CheckPriority1CompleteYN_AfterUpdate

Open in new window

While the first line I can manage to do it as the custom function ApplyLabelCheck accepts a variable, how do I go about the second one?

Thanks,
Ben
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What's in this:

CheckPriority1CompleteYN_AfterUpdate

 routine right now and is it different for each of your checkbox controls?

Jim.
0
 
bfuchsAuthor Commented:
Hi,

Yes, each has their sets of fields they update, mainly for history purposes.

see example
Private Sub CheckBackgroundCheckReqYN_AfterUpdate()
    If Me.CheckBackgroundCheckReqYN = True Then
        Me.BackgroundCheckReqDate = DATE
        Me.BackgroundCheckReqInit = GetInitial
    Else
        Me.BackgroundCheckReqDate = Null
        Me.BackgroundCheckReqInit = Null
    End If
End Sub

Private Sub CheckEducationVerCompYN_AfterUpdate()
    If Me.CheckEducationVerCompYN = True Then
        Me.EducationVerCompDate = DATE
    Else
        Me.EducationVerCompDate = Null
    End If
End Sub

Open in new window


Thanks,
Ben
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Got it now and understand what your doing and there's an old trick that will help.

If you refer to the .Text property of a control, you fire events as if the user had done something.

So in your  ApplyLabelCheck (), all you need to do is pass the name of the control.   Then do this:

  Me(strControlName).Text = <some value>

 and the BeforeUpdate and AfterUpdate events will fire on their own and any control specific code can reside where it usually does.

Jim.
0
 
PatHartmanCommented:
The .text property can only be referenced when the control in question has the focus.
0
 
bfuchsAuthor Commented:
@Jim, @Pat,

I tried that above (including set focus) and got error message "Object doesn't support this property.." when referring to the .text property.

See attached.

FYI- I'm using Access 2003.

Thanks,
Ben
untitled.png
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
is a text control yes?

Jim.
0
 
PatHartmanCommented:
The problem is that except for the .value property (which is the default), all properties actually refer to the control.  If the control name is the same as the bound column name, you can get away with referencing the column name in newer versions of Access.  If the control name is different from the bound column name, you MUST reference the control name.

When the control name is different from the bound column name, then the bound field has only a single property and that is .value.  All the other properties refer to the controls itself.  So If you have a field named Employee and it is bound to a control named text123 or even txtEmployee, when you type Me.Employee.  -- the only property is .value.  However, when you type Me.text123. or Me.txtEmployee.  -- you will see all the properties of a control.

You can even bypass displaying a changed value.  For example, most of my tables have a field named ChangedDT.  If I name my control txtChangedDT and then type:
Me.ChangedDT = Now() -- the date doesn't actually become visible on the form until I save the record.  However, if I type:
Me.txtChangeDT = Now()  - the date is visible immediately.
0
 
bfuchsAuthor Commented:
The control was a check box..so I changed to a text box and it worked.
Thanks to all participants!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now