0

How do I pass a variable to an event in VBA, when the event occurs after execution of my method/sub?

In Detail:

'My Sub''''''''''''''''''''''''''
Private Sub mySub()
   structCalculateDateValues 'calculates a number of dates that I want to be unlocked on my form
   fnCreateTable  'create a table will all dates form my database
End Sub

'Event handler for continous form'''''''''''''''
Private Sub Form_Current()
   'Some code to get the value of my Struct - structCalculateDateValues
   'Lock records that do not fall within of the date parameters of the values in structCalculateDateValues 
End Sub

The concept here is I want a form to show a record for all dates in a temp table, but only allow some of those records to be editable. This takes place in MS Access 2013. The way I have it set up is:

  1. Create a struct containing all date calculation
  2. Load values into temp table based on those calculations
  3. A continuous form that is bound to that table already exists
  4. Execution would normally end here
  5. As the form open it triggers the Form_Current Event Handler
  6. Pass struct values to that handler somehow??????

This is more a question of efficiency than anything else and a big question is how that event is triggered. I also want to avoid an answer that results in me writing my struct member values to the database (would be a cop-out). I think there has to be a way in VBA to pause execution to detect an event right?

Thanks,

Improve this question
3
  • set it as a module variable where my sub is private myStruct as xyz then assign and use. use structCalculateDateValues as a function with a return of your struct also maybe Commented Dec 13, 2016 at 19:43
  • If I understand this you are suggesting that I recalculate my Struct in the event handler. This is easy to do yes, and my struct is a module set up like you suggest. However, i'd like to save my processor the effort if possible. Essentially i'd be duplicating work (if I'm understanding you here). Commented Dec 13, 2016 at 19:53
  • so just use it, if you have private struct at the top, then you can use in current Commented Dec 13, 2016 at 19:58

1 Answer 1

Reset to default
1

You need a module-level variable. That way you can access it from anywhere within the module:

Option Explicit
Private MyValue As MyValueType '<---

Private Sub Some_ParameterlessEventHandler()
    MyValue.SomeMember = 42
End Sub

Private Sub SomeProcedureThatRunsAfterTheHandler()
    Debug.Print MyValue.SomeMember 'outputs 42
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Won't a global or module level variable be lost to garbage collection? I believe in VBA after my Main Sub ends the references are released and all variables are garbage collected, correct? Also, I don't have free rein with the event handlers concerning a continuous form. They are in a separate module inside their own access object.
VBA does no garbage collection, it's reference-counted, and it won't "reset" until the End keyword is encountered or your macro bursts into flames.
@JamieMarshall note: by End I mean the actual End instruction, not the End part of the End Sub token. As long as the host application is alive, module variables live, unless you kill 'em with the End nuke.
Your solution works! I'm surprised. What references still exist to keep a variable alive where there is no code execution within the module? Thanks by the way.
It's just "there", sitting in global space - the execution context remains "alive" until you kill it with End (either in code or in the immediate pane - not sure pressing the 'Stop' button does the same, but it's possible), or until the host application exits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.