Is there a way to call the BeforeUpdate event procedure of any control in VBA (MS Access 2002 or 2003) ?

Question

Hi, In VBA, I'm changing the value of a few controls in an Access form. I like to run the BeforeUpdate events of these controls after doing so, as it checks the coherence between fields :

Private Sub ExampleProc1()
  Dim intCancel as Integer

  intCancel = False

  Me.Controls("Date1").Value=Null
  Me.Controls("Textfield1").Value=Null

  Call Date1_BeforeUpdate(intCancel)
  Call Textfield1_BeforeUpdate(intCancel)
End Sub

I'd like to make it generic, but I can't find a way to run the event procedure. I'd like something like that :

Private Sub ExampleProc2(ParamArray Fields())
  Dim intCancel as Integer, varV as Variant

  For Each varV in Fields
    Me.Controls(varV).value=Null
    intCancel = False
    Call Me.Controls(varV).BeforeUpdate(intCancel)
  Next
End Sub

Is this even possible? Maybe something with DoCmd.RunMacro is the way to go?

Answer 1

Apparently it is possible to do CallByName in Access, but you could also put your BeforeUpdate logic for all your controls in a centralized sub, varying its behavior based on the parameters you pass. Then all you would have in your BeforeUpdate event handlers would be a call to your centralized subroutine.

Answer 2

Actually you can do it using CallByName

CallByName Me, Me.Controls(varV).Name & "_BeforeUpdate", VbMethod, intCancel

Answer 3

You may wisg to consider Run (http://msdn.microsoft.com/en-us/library/aa199108(office.10).aspx) and Eval Function:

Debug.Print Eval("ShowNames()")    
Debug.Print Eval("StrComp(""Joe"",""joe"", 1)")
Debug.Print Eval("Date()")

Answer 4

You might want to look into creating custom collections and then passing those to your functions. I use them extensively in forms for validating controls. Say at the form's module level you defined this collection:

  Dim mcolDateFields As New Collection

Then in your form's OnLoad event you could do this:

  mcolDateFields.Add Me!txtDateEntered, "txtDateEntered"
  mcolDateFields.Add Me!txtDatePrinted, "txtDatePrinted"
  mcolDateFields.Add Me!txtDateArchived, "txtDateArchived"

To walk through that collection, you'd do something like this:

  Dim varItem As Variant
  Dim ctl As Control

  For Each varItem In mcolDateFields
    Set ctl = varItem
    Debug.Print ctl.Name & ": " & ctl.Value
  Next varItem
  Set ctl = Nothing

...where you'd replace the Debug.Print with something that's actually useful.

You can also then pass the control to other code routines that take a collection as a parameter and do something with it:

  Public Sub (mcolCollection As Collection)
    Dim varItem As Variant
    Dim ctl As Control

    For Each varItem In mcolCollection 
      Set ctl = varItem
      Debug.Print ctl.Name & ": " & ctl.Value
    Next varItem
    Set ctl = Nothing
  End Sub

This may allow you to pass a collection of controls that you want to validate. Since you are storing control references in your collection and not control names or control values, there's no need to pass the parent form name. If you needed it, you could get it with this:

  Dim varItem As Variant
  Dim ctl As Control

  For Each varItem In mcolCollection 
    Set ctl = varItem
    Debug.Print ctl.Parent.Name & "!" & ctl.Name & ": " & ctl.Value
  Next varItem
  Set ctl = Nothing

Likewise, if you'd like to use the form the same way you'd use the Me keyword in the form's own module, just use ctl.Parent, which returns a form reference if the control is on the form itself (and not, say, on a tab control).

Some comments on your code examples -- your first one looks like this:

  Private Sub ExampleProc1()
    Dim intCancel as Integer

    intCancel = False

    Me.Controls("Date1").Value=Null
    Me.Controls("Textfield1").Value=Null

    Call Date1_BeforeUpdate(intCancel)
    Call Textfield1_BeforeUpdate(intCancel)
  End Sub

While it's defined as an Integer when you create a cancellable event, it's really not at all -- it's a Boolean, and to cancel the event you have to set it equal to True (-1). I think this is actually a holdover in Access VBA from the days of Access before VBA, when Access Basic lacked a Boolean data type. In general, I only use Booleans for variables used with the Cancel parameter.

  Me.Controls("Date1").Value=Null

I don't know why you'd do it this way. I'd code that as this:

  Me!Date1 = Null

Your code is overly verbose in that you've twice specified defaults: the Controls collection is the default collection of the form. Actually, the default collection is a concatenation of the Controls and Fields collections, so there could be good reasons for specifying that you want to use a control, for instance if you're using a property of a control that the corresponding field lacks. But for manipulating the values, if there's a control with the same name as a field, it doesn't matter which value you use, as they will be identical, except in the highly unlikely event that the control is not actually bound to the field of the same name.

Likewise, .Value is the default property of controls, so there's no reason to specify it. The only circumstances in which you might want to do that is if you're using it as a parameter to a call to another routine where the parameter is ByRef and not ByVal. But in general, for just assigning a value to a control, it's a waste of typing.

To do what you're trying to accomplish, the collection of control references seems to me to be a good solution. I don't know if the events for those controls need to be public or not, though.