Global Variables in Access Query

[MSeda]

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

[MSeda]

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]

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.

[MMcCarthy]

I tested this and it worked for me...

 
Function GloVar(ByRef varName As String) As Variant
 
  GloVar = varName
 
End Function
 
 

[NeoPa]

Does this not return the string that is the name of the variable rather than the contents of the variable?

[MMcCarthy]

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

[MSeda]

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?

[MSeda]

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]

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

x=RptParms(2)

The string and number values would be returned with parameters of 1 and 3 respectively.

Does that make it usable?

[MMcCarthy]

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]

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