Assign Global Variable to TextBox Control Source (1 Viewer)

Lateral · Sep 23, 2018

Hi Guys,

I've spent hours trying to figure this out but just can't crack it.

Here is a simplified explanation:

I have a form (Form1) that has a tab control that has a number of Sub forms called subform1, subform2, subform3 etc. On subform1 there are text boxes called "TextBox1", TextBox2", "TextBox3" and "PartName"..

When TextBox1 is clicked, another form (Form2) is displayed.

As I am using Global variables to pass values between the forms (as this makes it easy for me to utilise this functionality throughout the application), when TextBox1 is clicked, the OnClick event sets the global variable called GBL_PartName = Me.[PartName] on subform1.

All of this works great.

On Form2 I have a textbox called "txtPartName".

I want to pass the Global variable, GBL_PartName to this textbox so that it is displayed on Form2.....

I though that I could simply do the following in the Control source of "txtPartName":

=GBL_PartName

But it doesn't like it.

I tried setting it via VBA in the On Current event when Form2 opens but that doesn't work either.

I also tried to get the PartName direct from form 1 by using the following in the control source of "txtPartName":

=Forms("Form1").[subform1].[Form].[PartName]

The above works but it causes me issues in that it makes it more difficult to reuse the functionality across different parts of the application.

Ok, so here are my questions:

1. how do I specify a Global variable as the Control source for my text box either via VBA or direct?

2. Is if possible to directly set the Control Source of the text box to pick the PartName from the Active form???

Thanks for your help with this as its giving me headaches!

Cheers
Greg

Uncle Gizmo · Sep 23, 2018

Why can't you use the child parent linking of the subform/subreport control?

Sent from my SM-G925F using Tapatalk

Lateral · Sep 23, 2018

Hi Uncle Gizmo,

I don't know how to do what you are suggesting....

arnelgp · Sep 23, 2018

you must define your glibal var in separate standard module.
its better to qualify the var with its module name:

module1.gbl_partname=me.partname

me.txtpartname=module1.gbl_partname

isladogs · Sep 23, 2018

Agree with arnel that you need to define your global variable(s) in a standard module.
For convenience I use a modDefinitions for listing all global/public variables and no other purpose.

I prefer to prefix all variables with it's datatype e.g str, lng, int, dte

However disagree about including the module name in use.
It serves no purpose that I can see.
Perhaps arnel can explain why he suggested it.

arnelgp · Sep 23, 2018

dont you know yiu can define same gkibal variable name in different modules. access wont complain even if you compile your code. if you have such you need to prefix it to the correct module.

isladogs · Sep 23, 2018

arnelgp said:
dont you know yiu can define same gkibal variable name in different modules. access wont complain even if you compile your code. if you have such you need to prefix it to the correct module.

Whilst you CAN do that, it seems a fairly dumb thing to do.

If its a global (or public) variable, it should apply across the whole application.
If its specific to one bit of code / one module, use Dim/Private instead

That's one reason why I define all global/public variables in the same module ... so they aren't repeated!

arnelgp · Sep 23, 2018

it might not be of use ti you. think of it as a clas. evey class has simewhat same member name.

isladogs · Sep 23, 2018

In class modules, I normally use Private/Dim.
Where it needs to be Public, I try to use unique names to prevent possible issues

Having said that I've just looked in an old app of mine and found these variables in three of the class modules developed almost 20 years ago by an ex-colleague of mine

Not only are some of the names identical but in the highlighted cases not even the same datatype!
Even though this hasn't caused issues in the past 20 years, I'll add this to my list of jobs to do!

Galaxiom · Sep 23, 2018

arnelgp said:
dont you know yiu can define same gkibal variable name in different modules. access wont complain even if you compile your code. if you have such you need to prefix it to the correct module.

Yes. Access won't complain until they are are referenced at run time from outside the module without being qualified. Inside their own module they don't need qualification and the reference will find the most immediate scope just as happens inside a Class when a global variable shares the same name.

There is nothing actually wrong with having the same module-scoped variable names in different modules. The point of having different modules is to ensure there is no possibility of ambiguity. However these variables should never be declared as Public.

True Global Variables are Public and all those in the project should always be declared in same module to avoid such problems.

It is another good reason to avoid Global Variables as much as possible.

Galaxiom · Sep 23, 2018

ridders said:
Having said that I've just looked in an old app of mine and found these variables in three of the class modules developed almost 20 years ago by an ex-colleague of mine

Not only are some of the names identical but in the highlighted cases not even the same datatype!
Even though this hasn't caused issues in the past 20 years, I'll add this to my list to jobs to do!

Nothing really needs to be done. The given examples are valid practice in a Class Module.

Having said that, I am not a fan of Public variables being exposed as a member of a Class. Best practice keeps the variables private and addresses them through Properties or Methods.

Arnelg is talking about using Public Variables in Standard Modules as though they are an instance of a Class. Some developers do it but to me it is misuse because it conflates quite different functionalities and invites ambiguity.

isladogs · Sep 23, 2018

Galaxiom said:
Nothing needs to be done. That is perfectly normal practice in a Class Module.

Arnelg is talking about using Public Variables in Standard Modules as though they are an instance of a Class. Some developers do it but to me it is misuse because it conflates quite different functionalities and invites ambiguity.

Thanks Greg
Just to check ... are you saying that as these are all class modules, there is no need to use the same datatype for public variables such as pPupilID in each module?
As I said before, I didn't write these class modules though I did build upon them MANY years ago.
Would need to check why public variables are used here

Galaxiom · Sep 23, 2018

ridders said:
Just to check ... are you saying that as these are all class modules, there is no need to use the same datatype for public variables such as pPupilID in each module?

I don't know. It was just that you said they were Class modules in your post. There is no way to tell a Class Module from a Standard Module from those images, though the use of the Public variables suggests it. Only their location in the Project Explorer would make it obvious.

Would need to check why public variables are used here

They would be being used as Properties of the Class. This isn't as good a practice as declaring actual Properties because there is no real control over the values being fed to them. However in some cases it doesn't make much difference.

sonic8 · Sep 23, 2018

Galaxiom said:
There is no way to tell a Class Module from a Standard Module from those images, ...

The icon in the title bar of the windows gives it away.

sonic8 · Sep 23, 2018

ridders said:
... are you saying that as these are all class modules, there is no need to use the same datatype for public variables such as pPupilID in each module?

Technically no, they are all completely independent member variables and the compiler does not care about them having the same name.
However, from a human point of view, two variables with the same name having different data types is a bit fishy. The only valid reason I can imagine right now is that some of them are allowing NULL while others don't. Then they should be either Variant or any other distinct data type throughout.

PS: An ID being Integer is in itself something that should be questioned. Of course it might be intended and not cause for any concern, but I frequently see this when people from another programming language background do VBA and expect an Integer to be an Int32, which it is not.

isladogs · Sep 23, 2018

Thanks Phil and Greg

They are indeed class modules as already stated.
Now I've noticed this discrepancy (after 15 years or so...), I will modify the 'incorrect' datatypes to what these should be.
The correct values are string for pPupilID and long for pPRecordID.
However, interesting that there has never been an issue in all these years!

sonic8 · Sep 23, 2018

Lateral said:
1. how do I specify a Global variable as the Control source for my text box either via VBA or direct?

I'm surprised that nobody has pointed out the obvious issue here, yet.
You simply can't use a global variable in the Controlsource directly. Instead you could use a function to retrieve the value of the global variable.

Code:

Public Function GetGBL_PartName() 
    GetGBL_PartName = GBL_PartName
 End Function

Then reference the function in your ControlSource as =GetGBL_PartName()

MajP · Sep 23, 2018

The whole thing looks strange to me. It looks like you are passing a record by global variables. Makes no sense and a ton of unneeded work. My guess you could radically simplify what you are doing. Likely pass an ID to a form and populate with the correct data. Or pass a query or recordset limited to a single record. If you really felt compelled to do this those should definitely be class modules. I agree with Ridder it would be much better to turn them into private class variables and have let get properties. You already have a Recordset class if I want field values, I cannot see any reason to do this.

Uncle Gizmo · Sep 23, 2018

Lateral said:
Hi Uncle Gizmo,
I don't know how to do what you are suggesting....

I got the wrong end of the stick with your question. Although I think my question does have some merit in that maybe your Design is a little out of kilter. But I am in no position to judge that from the information provided, it's just a guess.

There is an alternative to using a global variable, I demonstrate in the attached database.

AssignGloVarTxtBoxCtrlSource_1a.accdb

If you still need a global variable there's no reason that you shouldn't set one. Although it always seems to me that global variables are a shortcut to doing it properly. As others have indicated custom properties would be a better bet.

I had to make some slight changes to your original specification, I used:- "txtBox1" instead of "TextBox1" because my code relies on there being a three character prefix.

Uncle Gizmo · Sep 23, 2018

An interesting feature of my method is that if you add the following line of code:-

Code:

CallCalled.prpPrincipleCtrl = Me.txtPartName

Either place it in the forms Close event or in the onclick event of the command button. Change the value shown in Form2 (change "Part" to "uncle gizmo is a genius") and close the form or press the command button whichever method you have chosen and it will pass "uncle gizmo is a genius" back into the Calling Form...

Assign Global Variable to TextBox Control Source (1 Viewer)

Registered User.

Nifty Access Guy

Registered User.

..forever waiting... waiting for jellybean!

MVP / VIP

..forever waiting... waiting for jellybean!

MVP / VIP

..forever waiting... waiting for jellybean!

MVP / VIP

Attachments

Super Moderator

Super Moderator

MVP / VIP

Super Moderator

AWF VIP

AWF VIP

MVP / VIP

AWF VIP

You've got your good things, and you've got mine.

Nifty Access Guy

Attachments

Nifty Access Guy

Similar threads

Users who are viewing this thread