Solved

how to call a sub using a string variable?

Posted on 2016-11-01
20
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +3
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 4

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 4

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 51

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 58
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 38

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 4

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
 
LVL 58
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 4

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 58
ID: 41871903
What's in this:

CheckPriority1CompleteYN_AfterUpdate

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

Jim.
0
 
LVL 4

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 58

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 38

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 4

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 58

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 38

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 4

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

624 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