Updating Form Using Form Events Rather Than Control Events

[Lenny5160]

I have an Access 2003 form with 58 checkboxes. Each checkbox has 2 corresponding combo boxes, which I would like to keep hidden until their box has been checked. The naming is consistent, with the checkboxes named "chkName" and the combo boxes named "cboName_Rating " and "cboName_Durati on".

Since there are so many checkboxes, I want to eliminate having to put a function call in the AfterUpdate event of each one. Here is what I am trying to use on the Form level, but cannot find the appropriate event to attach it to, if there is one:

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Dim nm As String
  Dim nmLong As String
  
  If Me.ActiveControl.ControlType = acCheckBox Then
  
    nm = Mid(Me.ActiveControl.Name, 4)
    
    If Me.ActiveControl = -1 Then
      nmLong = "cbo" & nm & "_Rating"
      Me(nmLong).Visible = True
      nmLong = "cbo" & nm & "_Duration"
      Me(nmLong).Visible = True
    Else
      nmLong = "cbo" & nm & "_Rating"
      Me(nmLong).Visible = False
      nmLong = "cbo" & nm & "_Duration"
      Me(nmLong).Visible = False
    End If
  
  End If

End Sub

I know the code is good because it will work on the last changed checkbox if the form is saved, but I can't get it working in real-time as each box is checked.

I am probably just creating more work in being lazy, but it seems like a good theory to me. Thanks in advance for any help!

Tony Leonard

[Tony Hine]

This thread here may provide some insperation, or possibly persperation!

[Lenny5160]

Thanks, but those all look like they require an additional button to be pressed to run the code. I am looking for a Form event that would be triggered whenever any data on the form is changed. I might just need to bite the bullet and call my code in the AfterUpdate event on each of the 58 checkboxes.

[NeoPa]

...
I am probably just creating more work in being lazy, but it seems like a good theory to me. Thanks in advance for any help!

Tony Leonard

Tony, since when was lazy a bad thing in a programmer?
I will try to look at this for you and see what I can come up with.

[NeoPa]

Tony,

I'm surprised you managed to get this code to trigger at all. Mine didn't.
The only way i would imagine it would, would be if the form fields were bound to a record source.

That's possibly the case. Is it?

[NeoPa]

...
I know the code is good because it will work on the last changed checkbox if the form is saved, but I can't get it working in real-time as each box is checked.
...

I just reread the original post and now realise the question is sort of in two parts.

1. Why doesn't my code work for each update?
2. Is there a Form event that will trigger when a (bound) CheckBox value is changed?

Answers

1. The Form_BeforeUpda te event procedure is triggered only when a save occurrs. This is often simply when you move from one record to another. It doesn't trigger for each change on the form.
2. No. I'm afraid there's not. Even the Form's Click event only triggers on the form if there is no control effected.

What to do?

I would consider writing a sub to do the main part for you, then create control_AfterUp date event procedures that simply call this sub.

Private Sub ShowHide()
  Dim strName As String

  With Me
    If .ActiveControl.ControlType <> acCheckBox Then Exit Sub

    strName = "cbo" & Mid(.ActiveControl.Name, 4)
    .Controls(strName & "_Rating").Visible = .ActiveControl
    .Controls(strName & "_Duration").Visible = .ActiveControl
  End With
End Sub

Private Sub chkName1_AfterUpdate()
  Call ShowHide()
End Sub

Private Sub chkName2_AfterUpdate()
  Call ShowHide()
End Sub

Private Sub chkNamen_AfterUpdate()
  Call ShowHide()
End Sub

It's not as tidy as you would have liked, but it's better than full kit and caboodle option.

[Lenny5160]

Yes, I was planning to write a standalone sub or function that would be called by each AfterUpdate event if that is how it needed to be. Your way is a little neater than mine though. Thanks!

I pasted the BeforeUpdate sub because that is the last place I tried the code. At the same time, I was also moving a "DoCmd.Save " among the Click, Dirty, and MouseDown events hoping they would in turn trigger the BeforeUpdate event. No such luck.

I really appreciate the effort!

[NeoPa]

A pleasure Tony.

I needed to brush up on some of those issues anyway so it was interesting :)