By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Updating Form Using Form Events Rather Than Control Events

P: 3
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_Duration".

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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.   Dim nm As String
  4.   Dim nmLong As String
  5.  
  6.   If Me.ActiveControl.ControlType = acCheckBox Then
  7.  
  8.     nm = Mid(Me.ActiveControl.Name, 4)
  9.  
  10.     If Me.ActiveControl = -1 Then
  11.       nmLong = "cbo" & nm & "_Rating"
  12.       Me(nmLong).Visible = True
  13.       nmLong = "cbo" & nm & "_Duration"
  14.       Me(nmLong).Visible = True
  15.     Else
  16.       nmLong = "cbo" & nm & "_Rating"
  17.       Me(nmLong).Visible = False
  18.       nmLong = "cbo" & nm & "_Duration"
  19.       Me(nmLong).Visible = False
  20.     End If
  21.  
  22.   End If
  23.  
  24. 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
Mar 10 '08 #1
Share this Question
Share on Google+
7 Replies


P: 3
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.
Mar 10 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
...
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.
Mar 11 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
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?
Mar 11 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
...
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_BeforeUpdate 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_AfterUpdate event procedures that simply call this sub.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ShowHide()
  2.   Dim strName As String
  3.  
  4.   With Me
  5.     If .ActiveControl.ControlType <> acCheckBox Then Exit Sub
  6.  
  7.     strName = "cbo" & Mid(.ActiveControl.Name, 4)
  8.     .Controls(strName & "_Rating").Visible = .ActiveControl
  9.     .Controls(strName & "_Duration").Visible = .ActiveControl
  10.   End With
  11. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub chkName1_AfterUpdate()
  2.   Call ShowHide()
  3. End Sub
  4.  
  5. Private Sub chkName2_AfterUpdate()
  6.   Call ShowHide()
  7. End Sub
  8.  
  9. Private Sub chkNamen_AfterUpdate()
  10.   Call ShowHide()
  11. End Sub
It's not as tidy as you would have liked, but it's better than full kit and caboodle option.
Mar 12 '08 #6

P: 3
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!
Mar 12 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
A pleasure Tony.

I needed to brush up on some of those issues anyway so it was interesting :)
Mar 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.