Global Variables

[Adrian510]

Could anybody point me in the direction of a simple / step by step tutorial on global variables. I have searched the site and read through other threads but I am still having trouble defining the listbox selections on my forms to a global variable module so that i can use them in other forms

Regards

Adrian

 

[FoFa]

What I usually do is in a module create the global variables (global VarName as datatype, or public Varname as Datatype depending on the version of access) in the declarations section. Then all you have to do is assign the value at the proper time. Could be the ON Click Event (which is what I usually use).

 

[Adrian510]

I have a handle on the theory but i am having trouble setting it up in practice what i really need is a very simple step by step walk through preferably with examples.

I thought it might have been a common query so yourself and the other members who have a lot high level of experiance than myself would have come across and therefore could point me in the direction of a website or link?

Adrian

 

[FoFa]

OK, Create a modual and setup your global variabl/s.
On the OnClick event of the listbox, store the bound column.

Later you can use the bound column to get other information, or you can store the column information from the list box (not shown) in the same fashion, you just have to pull it out via the column.

 

[Adrian510]

Thanks for the reply's FoFa I have ceated a standard module - GlobalVariables and a variable within it as GLB_CustomerId and using the immediate window this is working just great. However i can't seem to refernce it, specifically i am trying to restrict the contents of a list box by using

SELECT tblProducts.FinishedPartCode, tblProducts.Description, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.CustomerId_FK)=[GLB_CustomerId]));

I know it is probably to do with referencing the module that the variable is in but i don't know how to do this in SQL view and i can't get to it using the expression builder either. Could you help any further?

Thanks

Adrian

 

[FoFa]

You can't reference a variable in a query directly. Usually what I do is create an invisible field on the form, set that to the value of the global variable and reference the form field in the query. You could create a dynamic query, but that is harder.
SELECT tblProducts.FinishedPartCode, tblProducts.Description, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.CustomerId_FK)=Forms!MyForm!MyField));

 

[Adrian510]

I can't even set the value of a text box to the global variable it is not available as an option in the drop down list in Control Source (this i was expecting) and when i go into the expression builder i can pick up the GlobalVariables module within Functions but the last selection box is blank and i was expecting a <value> that i could select.

This is really begining to get to me any help appreciated.

Adrian

 

[Bat17]

You can use a function to pass the value of the variable.

Dim GLB_CustomerId as Long

function GetGLB_CustomerId()
GetGLB_CustomerId = GLB_CustomerId
End function

SELECT tblProducts.FinishedPartCode, tblProducts.Description, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.CustomerId_FK)=GetGLB_CustomerId()));


Air code but it should be close

HTH

Peter

 

[FoFa]

Yes, variables are really only visible to VBA with access, BAT17's solution will work for you, and you are limited in this regards.

 

[Adrian510]

Thanks FoFa and Peter I came in this morning refreshed and determined to beat this issue (albeit with slightly less hair after pulling it out yesterday) and Peter's solution worked great. I can also understand what is going on so hopefully the problem will never darken my door again (give a man a fish as opposed to teaching how to fish and all that).

Thanks again for all you efforts

Adrian

 

[Adrian510]

In case this thread is useful to other people here is another link that i found initially which came up with the same solution but when i read it initially it didn't make sense. It does know after it has been "worked through" above

http://www.access-programmers.co.uk/forums/search.php?searchid=713733