Question about sharing variables between procedures, and passing variables as arguments in an event procedure

Question

Hey guys,

Do you have anywhere I could read up on how variables are handled in vba? I know you can pass variables between procedures and functions by value and reference. One thing I'm not sure about is how I can pass a variable from one procedure to another procedure that is invoked by an onClick action in a form. I'm looking for a global variable that will keep its contents between procedures. I ve tried creating a variable that should be shareable between procedures as shown here 

http://msdn.microsoft.com/en-us/library/office/gg264241(v=office.15).aspx 

And the variable is in fact shareable but the contents of the variable are discarded.

My purpose is to build an Update Form where you first find the records you want to Update, then there s a list of entities generated from the keywords used. I then feed each entity to the update form where all the information is displayed to be updated. In order to scroll through this list I have arrow buttons to go forward an entity or back an entity. And I ve come to a problem where i don't know how to keep track of where i am in the recordset that i use as my data structure to keep the primary keys. Essentially back and forward buttons shouldn t do more than rst.MovePrevious and rst.MoveNext.

Answer 1

I realize now that when the onLoad procedure starts, it also finishes and therefore ends the execution of the procedure and the life of that variable. And this is why when I try to use the same variable in the two onClick procedures, the variables are empty.

That is only true if the variable in question is declared inside the Form_Load procedure.  As I said before, if a variable is declared at the module level (on a form), it "lives" as long as that form is open, and is visible to all procedures inside that form's module.  It isn't visible to procedures *outside* that form's module -- on some other form, for example -- unless the variable is also declared as Public, and references to it from outside are qualified with a reference to the form containing the variable;  e.g.,

    Forms!FormName.MyPublicVariable

It's also possible to declare Public variables at the module level in *standard* modules, in which case their lifetime is the lifetime of the application (except in certain circumstances), and references to them don't need to be qualified in any way.  These are often referred to as "global" variables.  However, using them reliably requires a certain amount of care, so I don't recommend them unless you know what you're doing.

Now, with all that said, I'm not sure why you need module-level or global variables for this.  It seems to me that the information you need to move to the "next" or "previous" Entity to be updated is present on the form from which the UpdateEntity form was called.  What's to stop you from referring to that form, in your code, to find out which Entity is "next" or "previous"?  Either that, or that form coulld pass, via the OpenArgs argument of the OpenForm method, a list of all the selected Entities, which the UpdateEntity form could save in a hidden control and interrogate as needed.  You haven't given me the details of the forms and controls involved, so I can't get specific, but controls on forms (whether visible to the user or not) can often be used as convenient places to stash data.

Answer 2

Stashing data works very well, however A list box has a limited number of characters per row, and it doesn t work well when i want to stash string sqls since they re usually longer than 32 characters or whatever the limit is.

Answer 3

I was thinking about stashing data in tables. I ll do it by stashing data in a list box and treat it as an array. I will let you know how it goes.

Thanks.

Answer 4

To paint the picture this is what happens

user inputs information into a findEntity form to find relevant entities in the database. Then user clicks update which takes the user to a form with the search results, where the user can select the entities to update, you can have 0, 1 or more entities to update. Then the user clicks update and it takes the user to another form with textboxes that are prefilled with information of the first entity selected. This happens as an onLoad event of the UpdateEntity form. From there the user can choose to edit the information of the first entitiy, then click update to update that information, or the user can click right or left arrow buttons to fill the text boxes based on the next entity. the entities are stored in a RecordSet. The onLoad procedure takes the first Entity's primary key, to pull the infromation into the text boxes, and my desired functionality is for right and left buttons to act as rst.MovePrevious and rst.MoveNext so that I can browse through the entities, and fill text boxes accordingly.

I realize now that when the onLoad procedure starts, it also finishes and therefore ends the execution of the procedure and the life of that variable. And this is why when I try to use the same variable in the two onClick procedures, the variables are empty.

How can I keep track of where i should be within the recordset and which entity to pull in.

Answer 5

A variable declared at the module level -- that is, in the General section of the module, outside of any procedure -- will be known to all the procedures in that module, and will have a lifetime equal to the lifetime of the module.  So module-level variables are useful for sharing information among event procedures within a form module.

I don't have a good enough picture of what you're trying to do with your Update Form to be able to advise you on that.  Is it a module-level recordset you're thinking of using?