Solved

how to call a sub using a string variable?

Posted on 2016-11-01
20
57 Views
Last Modified: 2016-11-08
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
Comment
Question by:bfuchs
  • 7
  • 5
  • 3
  • +3
20 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41869391
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
 
LVL 75
ID: 41869412
Seriously ... why not just change it to a Function. There is zero advantage of a Sub in today's world.
Done ! Boom!
0
 
LVL 3

Author Comment

by:bfuchs
ID: 41869504
@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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41869533
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
 
LVL 3

Author Comment

by:bfuchs
ID: 41869538
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41869889
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
 
LVL 57
ID: 41869917
<<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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41870484
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
 
LVL 75
ID: 41870987
RE "how could I change it to public/function?"

Sorry I missed this was a built in Sub ...
0
 
LVL 3

Author Comment

by:bfuchs
ID: 41871087
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 57
ID: 41871100
<<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
 
LVL 3

Author Comment

by:bfuchs
ID: 41871132
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
 
LVL 57
ID: 41871903
What's in this:

CheckPriority1CompleteYN_AfterUpdate

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

Jim.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 41872842
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41873878
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41874233
The .text property can only be referenced when the control in question has the focus.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 41876364
@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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41879233
is a text control yes?

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41879332
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
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 41879568
The control was a check box..so I changed to a text box and it worked.
Thanks to all participants!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now