I would like to use a Global variable as a criteria in an access query.
in my first attempt to do so I wrote a Function that looks like this
Public Function GloVar(ByVal VarName) As String
GloVar = VarName
End Function
It Didn't Work.
It evaluates to the text of the variable name not the value of the variable. Additionaly access forces the VarName to be in quotes. If I Type GloVar(JobNo) it automatically changes to GloVar("JobNo").
I read a post from about a year ago that suggested using an If statement for each variable. i.e.
If VarName = "JobNo" Then
GloVar = JobNo
I (as the original poster) was hoping for a more universal function that would retrieve the value of whatever variable was entered without have to introduce an if statement for every variable.
Any help will be appreciated.
Megan
19 17926 NeoPa 32,556
Expert Mod 16PB
Am I right in thinking that you want a function (GloVar) to return the value (contents) of a variable, whose name (in the source code) matches the string passed in VarName?
Essentially this is impossible as the compiler is not running when the code is, so there is no access to the source code.
Strangely, VBA DOES provide a way (Eval() function) whereby you can call a function 'indirectly' in this way, but not to access a variable.
Interesting question.
You understand pretty well what I want to do.
Specifically what I want to accomplish is this.
When a job is created via the "Create New Job Form" the global variable JobNo is assigned the Job Number. which is passed to subsequent forms i.e. "Create a purchase order".
I would like to be able to use the variable as criteria in a query. I am attempting to eleminate some of the bulky queries and code in my database and this seems like the simplest solution since the global variable can maintain its value while forms open and close during the job creation process and without the need for flags at the table level.
I am currently using a function that looks like this
Public Function GloVar(ByVal VarName) As String
Select Case VarName
Case "CurEmp"
GloVar = CurEmp
Case "CurEquip"
GloVar = CurEquip
Etc... for each variable
End Select
End Function
This method requires each variable be entered as a case into the function. I was hoping for something a little more universal that could be used with any variable.
I appreciate the input.
Megan
NeoPa 32,556
Expert Mod 16PB
You can do what you're doing already, or alternatively, do a function for each variable.
As I explained in the previous post though, what you'd like to do is not supported in VBA (for the reasons I explained in said post it wouldn't make sense).
PEB 1,418
Expert 1GB
In fact Eval uses the visible objects from the modules in VBA...
And the global variables are visible only in modules but nowhere else..
Thill the functions are visible everywhere..
It's good solution your one using the select case method to show your global variables...
:)
Thanks for the Input. I guess I'll stick with the case select function.
NeoPa 32,556
Expert Mod 16PB
This function might prove helpful, although :
1. It works by ordinal (number of position in list) rather than name.
2. I haven't tested that it works independantly of my other routines (thug it passed a manual scan ;) ). - 'RptParms sets and returns a set of parameters required by a report.
-
Public Function RptParms(intSetGet As Integer, _
-
ParamArray avarParams() As Variant) As Variant
-
Static avarParms() As Variant
-
Dim intIdx As Integer
-
-
RptParms = 0
-
If intSetGet = 0 Then
-
intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
-
If intSetGet < 1 Then
-
ReDim avarParms(1 To 1)
-
avarParms(1) = "Error"
-
Exit Function
-
End If
-
ReDim avarParms(1 To intSetGet)
-
For intIdx = 1 To intSetGet
-
avarParms(intIdx) = avarParams(intIdx - 1)
-
Next intIdx
-
Else
-
'If outside bounds then it drops through and is set to "Error"
-
On Error Resume Next
-
If avarParms(intSetGet) = "Error" Then
-
RptParms = "Error" 'On Error
-
Else
-
RptParms = avarParms(intSetGet)
-
End If
-
End If
-
End Function
I would like to use a Global variable as a criteria in an access query.
in my first attempt to do so I wrote a Function that looks like this
Public Function GloVar(ByVal VarName) As String
GloVar = VarName
End Function
It Didn't Work.
It evaluates to the text of the variable name not the value of the variable. Additionaly access forces the VarName to be in quotes. If I Type GloVar(JobNo) it automatically changes to GloVar("JobNo").
I read a post from about a year ago that suggested using an If statement for each variable. i.e.
If VarName = "JobNo" Then
GloVar = JobNo
I (as the original poster) was hoping for a more universal function that would retrieve the value of whatever variable was entered without have to introduce an if statement for every variable.
Any help will be appreciated.
Megan
Assuming varName is declared as a string
Global VarName As String
You are returning a string using the function you described
Instead declare VarName as a Variant and return a Variant from the function. This will allow for different datatypes.
Global VarName As Variant -
-
Public Function GloVar(ByVal VarName) As Variant
-
-
GloVar = VarName
-
-
End Function
-
-
Unfortunately changing to a GloVar to a variant still returns the name of the variable, or whatever text is entered as varname. I'm using the Case Select function for now. But I definately would prefer the simpler solution if you think of anything.
Thanks
Unfortunately changing to a GloVar to a variant still returns the name of the variable, or whatever text is entered as varname. I'm using the Case Select function for now. But I definately would prefer the simpler solution if you think of anything.
Thanks
Can you post the query statement in which you are trying to use this function.
This query subtracts used inventory from instock inventory:
UPDATE [Inventory Stock Table] SET [Inventory Stock Table].[Inv Part Quan In Stock] = GloVar("StkQn")-GloVar("QnUse")
WHERE ((([Inventory Stock Table].[Inv Stock #])=Glovar("StkID")));
this query creates an Inventory Transmital for a job that was just created:
INSERT INTO [Purchase Order Table] ( [Job#], [PO/IT #], Vendor, [PO Acct], [PO Bus Loc], [PO Parts Ordered by] )
SELECT [Job List Table].[Job #], "IT " & [Job #] AS PIT, "INVENTORY" AS Vendor, 11070 AS Acct, [Job List Table].[Job Business Location], [Job List Table].[Job Initiated By]
FROM [Job List Table]
WHERE ((([Job List Table].[Job #])=GloVar("JobNo")));
here are two examples. I use it mostly in action queries that require I close a form to release the table lock before I can perform a query.
NeoPa 32,556
Expert Mod 16PB
MSeda,
Have you looked at the function I posted earlier?
It doesn't use a variable name, as such, but enables you to set a number of Variants in code then refer to them later by their ordinal (or number in the list they occur).
This might do what you want, but perhaps not in quite as swish a way as desired.
I tested this and it worked for me... -
-
Function GloVar(ByRef varName As String) As Variant
-
-
GloVar = varName
-
-
End Function
-
-
NeoPa 32,556
Expert Mod 16PB
Does this not return the string that is the name of the variable rather than the contents of the variable?
Does this not return the string that is the name of the variable rather than the contents of the variable?
No
The ByRef seems to make it return the value.
Mary
I appriciate all of the suggestions. I looked at Neopa's use of ordinals but unfortunately it is a bit over my head programming-wise so I'm still using the case select function since it's on a level I can understand.
As far as the other suggestions they aren't working for me, maybe I'm missing something.
I tried the ByRef that mmcarthy posted but I get the #Name? error.
and as previously posted similar code using ByVal returns a string
The way I test to see if access recognizes the variables is I have a form with text boxes with the control source =GloVar("JobNo") or whatever the variable name is. (I put the variable name in quotes because access automatically brackets it if you don't quote it) I also have combo and text boxes that allow me to set the variables to appropriate values so I can test queries and stuff with out having to use the front end forms to create a new job in entirity.
I saw that Mary also said the ByRef worked for her too.
Right now I have just cut and pasted the code into a module and saved it, that usually works.Is there something I might not be doing or doing differently that is causing this not to work for me?
Sorry I made a mistake in my previous post. The byRef function also returns a string, I mistyped the name of the function in my test form.
NeoPa 32,556
Expert Mod 16PB
The function can just be copied into a module in your project.
The way it is used is to set up the variables in the first call where the first parameter = 0 and the others are those that you need to reference later. - Call RptParms(0, "A", Date(), 36)
If later, in a query, form or report, yo want the date saved you get it by calling
The string and number values would be returned with parameters of 1 and 3 respectively.
Does that make it usable?
By putting the variabe name in quotes you are passing a string value instead of the variable name. You can pass the variable name as a string deftype but if you put it in quotes the VBA will not recognise it as anything other than a string value. The only way you can use this in a query, if you cannot pass without the quotes, is by creating the query in VBA.
As these are action queries you can do this using runsql in VBA code as follows:
DoCmd.runSQL "UPDATE [Inventory Stock Table] SET " & _
"[Inventory Stock Table].[Inv Part Quan In Stock] = " & _
GloVar(StkQn)-GloVar(QnUse) & _
" WHERE ((([Inventory Stock Table].[Inv Stock #])=" & _
Glovar(StkID) & "));"
I appriciate all of the suggestions. I looked at Neopa's use of ordinals but unfortunately it is a bit over my head programming-wise so I'm still using the case select function since it's on a level I can understand.
As far as the other suggestions they aren't working for me, maybe I'm missing something.
I tried the ByRef that mmcarthy posted but I get the #Name? error.
and as previously posted similar code using ByVal returns a string
The way I test to see if access recognizes the variables is I have a form with text boxes with the control source =GloVar("JobNo") or whatever the variable name is. (I put the variable name in quotes because access automatically brackets it if you don't quote it) I also have combo and text boxes that allow me to set the variables to appropriate values so I can test queries and stuff with out having to use the front end forms to create a new job in entirity.
I saw that Mary also said the ByRef worked for her too.
Right now I have just cut and pasted the code into a module and saved it, that usually works.Is there something I might not be doing or doing differently that is causing this not to work for me?
PEB 1,418
Expert 1GB
Wow it seems genious
ByRef, it can do a great work!
I tested this and it worked for me... -
-
Function GloVar(ByRef varName As String) As Variant
-
-
GloVar = varName
-
-
End Function
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dante |
last post by:
Hi. I am designing a page and want to use global variables, but I
can't quite get them to work. For example I have this:
<?php echo $title ?>
....text...
<?php require_once ("colophon.php") ?>...
|
by: Patient Guy |
last post by:
I have a library of functions representing a filesystem interface
(essentially a file selection interface, to be used in
opening/reading/writing/closing files).
Heavily scripted HTML document...
|
by: Salvani Langosta |
last post by:
In an Access 97 database, I use serveral global variables that hold
information about the database, for example:
gstrFileServer - holds the server root where the database is stored...
|
by: Jean |
last post by:
Hello,
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID =...
|
by: Miguel Dias Moura |
last post by:
Hello,
I am working on an ASP.NET / VB page and I created a variable "query":
Sub Page_Load(sender As Object, e As System.EventArgs)
Dim query as String = String.Empty
...
query =...
|
by: Charles O'Flynn |
last post by:
As a complete newcomer (2-3 days) to PHP, although not to programming in
general, I have 'dived in' to start a small project to read and parse an XML
data stream. I have already worked out most of...
|
by: CDMAPoster |
last post by:
About a year ago there was a thread about the use of global variables
in A97:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/fedc837a5aeb6157
Best Practices by Kang...
|
by: weaknessforcats |
last post by:
C++: The Case Against Global Variables
Summary
This article explores the negative ramifications of using global variables. The use of global variables is such a problem that C++ architects have...
|
by: rottmanj |
last post by:
. In order to teach my self more. I have started to convert some of my cf scheduled tasks to perl applications.
One area where things are kind of fuzzy is setting up global variables that can be...
|
by: teddysnips |
last post by:
Weird.
I have taken over responsibility for a legacy application, Access 2k3,
split FE/BE.
The client has reported a problem and I'm investigating. I didn't
write the application.
The...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |