Stripping Variable values (1 Viewer)

[Zydeceltico]

Hi All -

I find myself passing the same 3 values under the same conditions over and over so I am considering creating public variables that I can call whenever I want to use that value instead of referencing forms as I have been which gets cumbersome sometimes.

My question - -after I have used a public variable in a form, saved the form, closed the form, and moved on to a new situation and want to use the public variable again in a different form or circumstance, is there some boilerplate code that I can use at the top of my sub or function that makes sure that the public variable I am calling does not have a value in it already?

Is it something like:

Private Sub Form_Load()
    myPublicVariable = ""
End Sub

Thanks!

Tim

 

[theDBguy]

Hi Tim. It depends on how you declared the variable. If a String, that would be how you'll "empty" it. Otherwise, you could try assigning Null to the variable. If you get an error, you know you'll have to use a different value.

 

[pbaldy]

That would work. If they'll all get cleared at the same time, I'd create a function that reset all 3 and call it from the appropriate events.

 

[Zydeceltico]

That would work. If they'll all get cleared at the same time, I'd create a function that reset all 3 and call it from the appropriate events.

So the variables are publicly declared this way:

Dim intInspectionEventID As Integer
Dim intJobID as Integer
Dim intFinalProductID as Integer

I am thinking then to create a Public Function like this:

Function ResetTheBigThree
intInspectionEventID Is Null
intJobID Is Null
intFinalProductID Is Null
End Function

and then at the top of the primary inspection form's class module I'd use:

Function ResetTheBigThree()
End Function

intInspectionEventID = Control1.Value
intJobID = Control2.Value
intFinalProductID = Control3.Value

Does that look right?

 

[pbaldy]

Well, it would be

intInspectionEventID = Null

but only a Variant can take a Null, so you'd either have to change how they're declared or set them to 0.

 

[Zydeceltico]

Well, it would be

intInspectionEventID = Null

but only a Variant can take a Null, so you'd either have to change how they're declared or set them to 0.

Since they are integers then I'd do this:

intInspectionEventID = 0 to reset them?

 

[pbaldy]

Yes. That data type can't take Null or "". If you wanted to use Null, you can change to

Dim intInspectionEventID As Variant

or to keep the prefix relevant

Dim varInspectionEventID As Variant

 

[Zydeceltico]

Yes. That data type can't take Null or "". If you wanted to use Null, you can change to

Dim intInspectionEventID As Variant

or to keep the prefix relevant

Dim varInspectionEventID As Variant

Those 3 variables will always and only be integers since they are all based on autonumber fields - so I am deducing that I would be better served by declaring them as integers rather than variants?

Is my thinking correct?

 

[pbaldy]

Yes, I offered the alternative if you wanted to be able to check them for Null to see if they were populated. You can just as easily test for 0, so I'd leave them as is.

By the way, the equivalent for an autonumber field is Long, not Integer. At some point you could start getting errors if the value got too big.

 

[Zydeceltico]

By the way, the equivalent for an autonumber field is Long, not Integer. At some point you could start getting errors if the value got too big.

I hope you know by now that I have zero shame in asking "dumb" questions or hearing that I have totally fundamental misunderstanding of basic concepts! LOL

I learn a lot from ya'll every day and I'm proud to say that my What-Once-Was-A-Thick-As-A-Brick db is becoming quite slick for it.

THX

 

[pbaldy]

Happy to help! There's no such thing as a dumb question, only a dumb answer.

 

[isladogs]

This all reminds me of your OpenArgs thread.

You are of course correct that a global variable value will persist until you clear it or change it....unless the application crashes or is closed.

Whilst you can certainly clear the values as you suggest, I'm not sure I see the point if you immediately follow that by setting the variable to a new value. Doing that will automatically clear the previous value.

One thing to note is that you cannot use a variable in filter criteria in a query.
However you can create a function and set that equal to the variable then use the function in your query.

Alternatively you could instead use tempvars which also persist, shouldn't be 'lost' in a crash... and can be used in queries.

 

[MajP]

I personally would try to do this another way. Public variables have a time and place (very very limited). For me the only time is after exhausting every other way without making things too complicated. Sometimes it is the only easy solution. You really want to control the scope and lifetime of all variables. You can get into a debugging nightmare.

If you are getting tired of typing
Forms("someForm").SomeControl

You could instead wrap these in function
so if you want the InspectionEventID, I will assume that comes from a loaded form. If that is the case I would make a public function

Public Function GetInspId() as Long
if currentProject.allforms("SomeFormName").isloaded then
GetInspId = forms("SomeFormName").InspectionEventID.value
end if
end function

then when I want to use it
X = getInspID 'returns 0 if not loaded.

If the issue is you need to pass all three of these at one time then consider creating a user type or class
https://www.bettersolutions.com/vba/data-types/user-defined-types.htm

Then hopefully you defined it in a way where it goes out of scope once you are done using it.

 

[Micron]

I've read this 2x and can't tell if it's understood that a variable declared as Public in a form module isn't available to any other form or report. It all seems to be about forms

after I have used a public variable in a form, saved the form, closed the form, and moved on to a new situation and want to use the public variable again in a different form or circumstance

Form2 can't see a variable declared as public in form1, even if form1 is open, no?

 

[Zydeceltico]

This all reminds me of your OpenArgs thread.

Yep. And that is a prime example of what I think I posted a long while back when I was working on the basic db design itself.

It's kind of like anything else. I'm a blacksmith and also a songwriter. I know all of those tools really well and can get super-creative with a particular set of tools - - because I know how to use them - really well.

When I started this (and still today) I knew where I wanted to get but I didn't know (or haven't known) how to use the tools - or that some tools even existed.

For me - the greatest thing about the folks on this forum is that whenever I pose a question - I receive several different ways to accomplish the task back from everybody. I end up gaining far more exposure to all of the various tools at my disposal. Some of them are more complex for me than others but I take note of all of them.

Like the rest of your post below. The value of that note is not lost on me. I'll use that info at some point.

I have a major luxury with this db as the company I work for has never had a QC db before. They've worked strictly on paper for the past 50 years. In other words, they're super happy to have anything at all and have very low expectations (at least from my vantage point) which results in me having an inordinate amount of time to really "slick" this out. .....and learn how to be a novice db designer at work.

Again - super grateful

Whilst you can certainly clear the values as you suggest, I'm not sure I see the point if you immediately follow that by setting the variable to a new value. Doing that will automatically clear the previous value.

It's been suggested to me to clear the variables just as good practice. However, I see your point if I clearly change the value there should be no reason to clear the preceding value.

One thing to note is that you cannot use a variable in filter criteria in a query.
However you can create a function and set that equal to the variable then use the function in your query.

Alternatively you could instead use tempvars which also persist, shouldn't be 'lost' in a crash... and can be used in queries.

Tempvars - - another thing to learn. Good to know that it persists that long.

 

[Zydeceltico]

I personally would try to do this another way. Public variables have a time and place (very very limited). For me the only time is after exhausting every other way without making things too complicated. Sometimes it is the only easy solution. You really want to control the scope and lifetime of all variables. You can get into a debugging nightmare.

If you are getting tired of typing
Forms("someForm").SomeControl

You could instead wrap these in function
so if you want the InspectionEventID, I will assume that comes from a loaded form. If that is the case I would make a public function

Public Function GetInspId() as Long
if currentProject.allforms("SomeFormName").isloaded then
GetInspId = forms("SomeFormName").InspectionEventID.value
end if
end function

then when I want to use it
X = getInspID 'returns 0 if not loaded.

If the issue is you need to pass all three of these at one time then consider creating a user type or class
https://www.bettersolutions.com/vba/data-types/user-defined-types.htm

Then hopefully you defined it in a way where it goes out of scope once you are done using it.

I can already see that is a better distilled solution for me as I get the logic of it and how I would use it.

They don't all get called at exactly the same time. They do all get called from within the same workflow over and over.

And yes - the scope of any value stored in one of those variables needs only be as long as a single line of workflow (open and close 3 forms in succession - all are open at the same time and Saved/Closed at the same time).

 

[isladogs]

I was saying much the same thing but I prefer to make my functions more generic.

So I might have a global/public variable defined in a standard module
E.g. Public lngClientID as Long.

Then in one or more forms set the value
E.g lngClientID = Me.ClientID

Then have a function GetClientID =lngClientID so I can use it in queries.

There's certainly nothing wrong with clearing a variable after use but if doing that, I normally do so at the end of a procedure in a 'tidy up' section. Doing so at the start of a different procedure seems illogical ..at least to me.

 

[The_Doc_Man]

There is a "gotcha" to be considered.

When you declare a Public variable in a general module, it changes in several different ways.

1. The general module gets loaded the first time that anything in it gets referenced - even if the first thing is a function or subroutine. At the point of module "load" you will have automatic instantiation of all variables, public or private, in that module at their default values. Which means the numerics become 0, the strings become "", the objects become Nothing, and the the variants become Null.

2. The variables are modified by any expression referencing same as the left-hand side of an assignment statement ... X = 2, MyName = "Mud", etc.

3. IF you have an unhandled exception such that you get the Debug prompt that lets you debug or just close the thing being debugged, you often have the Reset option. Which, if you use it, will reset the variables to their default initial state.

4. IF you choose to close the App completely but stay in the Access developer's screen, all such variables become de-instantiated and are therefore Null - i.e. NO value. Not zero, blank, or Nothing. At that point they are non-existent and therefore trying to use them will re-instantiate them all over again at default values (see #1 - but this is a chronologically different instantiation.)

As to the issue that causes you to have this idea, here is a common design flaw in line with what you described. You are looking at variables external to the class module. I have used this idea myself. Here is the pitfall - these "outside" variables are subject to conflicts of interest unless you very scrupulously track how each form/class module uses them. The general practice of using global or public variables leads to something called a "side effect" - which is not always good.

The questions about passing "OpenArgs" included that topic in which you have multiple forms open at the same time. If all of your forms use these three common variables and more than one is open at the same time, you expose yourself to destructive interference, a techie way of saying "conflict of interests." That would not be good at all.

 

[Cronk]

Those 3 variables will always and only be integers since they are all based on autonumber fields

If the variables are to be based on autonumber fields, then given autonumber is a long, you would be best to define your variables as Long

 

[pbaldy]

If the variables are to be based on autonumber fields, then given autonumber is a long, you would be best to define your variables as Long

I guess repetition is good.