CODEWITHSUNDEEP
vbams-accessevents
Jamie Marshall
Jamie Marshall

Reputation: 2306

Passing variable to VBA event handler after execution

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,

Upvotes: 0

Views: 1224

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

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

Upvotes: 1

Related Questions