1

I am trying to create variable which would carry across all the users activities in MS Access.

So what I want to achieve is: The user opens the application and sets a region.

From that point onwards I want to be able to reference the region in my VBA commands that are triggered as the user moves between forms to limit data being displayed. How do I carry the variable through the various forms and queries or is it just a case of creating it as a parameter that is passed to each procedure as it is called?

Thanks

Marc L
  • 837
  • 9
  • 19
  • 40

2 Answers2

1

Following article on ozgrid variables in VBA have three scoping levels:

  1. Procedure-Level
  2. Module-Level
  3. Project-Level, Workbook Level, or Public Module-Level

It appears that the third one would be most relevant in the context of your request. You would define it like that: 

Public SelectedRegion as Long

As an alternative approach, you may consider storing this selection in a file and reading the data from file. To introduce the relevant parts of the example available on the website:

myFile = "C:\test\geographical-coordinates.txt"
Open myFile For Input As #1
Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
Loop
Close #1
Konrad
  • 17,740
  • 16
  • 106
  • 167
  • Thanks, the public variable declaration works great. I'm avoiding having a session variable carried in a table due to multiple users updating simultaneously, this seems to be a better option. – Marc L Aug 17 '15 at 13:47
1

Konrad's answer covers most options, I just wanted to add that as you are using Access you could always create a user to region mapping table and store the data there.

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
  • Hi Problem is it is too dynamic- the users shift the region they are utilising almost daily depending on current focus- so having them select a region as they enter is the most effective means. – Marc L Aug 17 '15 at 13:46
  • Marc I think you are missing my point, how you collect the data and how often you collect it is irreverent, once you have captured it you need to persist it so that it can be used in other areas of your application. What better way is there to store data in a database application than in the database itself. How can data be too dynamic to store in a database, it's what they are designed to do. – Dave Sexton Aug 17 '15 at 14:06
  • The problem is with multiple users and getting the correct row from a table. If the region itself is not carried, then a session id or similar would need to be carried to identify the correct row in case another row is added while they are busy... If a variable is not being carried the computer does not know which is the correct line as it may not be the last record that should be referenced – Marc L Aug 17 '15 at 15:58
  • Just store the user's Windows user name alongside their region selection, see [this question](http://stackoverflow.com/questions/677112/how-to-get-logged-in-users-name-in-access-vba) for ideas on how this could be done. – Dave Sexton Aug 17 '15 at 16:25
  • Thanks Dave- that actually works perfectly! A really elegant solution! – Marc L Aug 17 '15 at 19:53