Event code apply to all objects

T

Tony WONG

The form looks like a questionnaire with 30 questions
When the user change the answer, then the questionnaire re-calculcate the
marks again

Now i add AfterUpdate event to every combo

But it is not professional to duplicate 30 copies similar codes in the code
page.

How can i add an event to apply to all the 3x Combos?

My Access version is 2007

thanks a lot.


****************************
Private Sub ComboQ2D_AfterUpdate()
Dim mark As Variant
If Me![Plan] = "A" Then
mark = DLookup("ScoreA", "Assessment", "Description='" & [ComboQ2D] & "'")
Me![TextQ2S] = mark
Me!Totalmark = Nz([Q1S]) + Nz([Q2S]) + Nz([Q3S]) + Nz([Q4S]) + Nz([Q5S]) +
Nz([Q5nS]) + Nz([Q6S]) + ...
Else
mark = DLookup("ScoreB", "Assessment", "Description='" & [ComboQ2D] & "'")
Me![TextQ2S] = mark
Me!Totalmark = Nz([Q1S]) + Nz([Q2S]) + Nz([Q3S]) + Nz([Q4S]) + Nz([Q5S]) +
Nz([Q5nS]) + Nz([Q6S]) + ...
End If
End Sub
 
T

Tom van Stiphout

On Tue, 23 Jun 2009 11:16:19 +0800, "Tony WONG" <[email protected]>
wrote:

If the code is IDENTICAL you can write it once as a private sub and
then call it from the 30 event procedures. If it is almost identical,
pass in the variable parts as procedure arguments.

THere is no way in VBA to write an event procedure and have it
AUTOMATICALLY be called. You must write an event procedure or at least
set the event property to the name of your function (I believe this
method does not work with subroutines)

-Tom.
Microsoft Access MVP
 
M

Marshall Barton

Tony said:
The form looks like a questionnaire with 30 questions
When the user change the answer, then the questionnaire re-calculcate the
marks again

Now i add AfterUpdate event to every combo

But it is not professional to duplicate 30 copies similar codes in the code
page.

How can i add an event to apply to all the 3x Combos?

My Access version is 2007

****************************
Private Sub ComboQ2D_AfterUpdate()
Dim mark As Variant
If Me![Plan] = "A" Then
mark = DLookup("ScoreA", "Assessment", "Description='" & [ComboQ2D] & "'")
Me![TextQ2S] = mark
Me!Totalmark = Nz([Q1S]) + Nz([Q2S]) + Nz([Q3S]) + Nz([Q4S]) + Nz([Q5S]) +
Nz([Q5nS]) + Nz([Q6S]) + ...
Else
mark = DLookup("ScoreB", "Assessment", "Description='" & [ComboQ2D] & "'")
Me![TextQ2S] = mark
Me!Totalmark = Nz([Q1S]) + Nz([Q2S]) + Nz([Q3S]) + Nz([Q4S]) + Nz([Q5S]) +
Nz([Q5nS]) + Nz([Q6S]) + ...
End If
End Sub
Click to expand...


You can put that code in a public Function procedure in the
form's module and the set all the control's AfterUpdate
event **property** to:
=thefunctionname()
instead of the usual [Event Procedure]

You can set all the contols' property in one shot by
selecting the controls by dragging a selection rectangle
around all of them or by holding down the Shift key and
clicking each control.

If your procedure needs to know the name of the control
object that triggered the event, you can get it by referring
to Me.ActiveControl

mark = DLookup("ScoreA", "Assessment", "Description='" _
& Me.ActiveControl.Name & "'")

If the controls were named with a common pattern such as
QxS, then you can use a loop to sum them:

total = 0
For x = 1 To 30
total = total + Nz(Me("Q" & x & "S"), 0)
Next x
 
T

Tom van Stiphout

On Mon, 22 Jun 2009 23:59:18 -0500, Marshall Barton

Lots of good suggestions.
Even a Private function in the form's CodeBehind will work.

-Tom.
Microsoft Access MVP

Tony said:
The form looks like a questionnaire with 30 questions
When the user change the answer, then the questionnaire re-calculcate the
marks again

Now i add AfterUpdate event to every combo

But it is not professional to duplicate 30 copies similar codes in the code
page.

How can i add an event to apply to all the 3x Combos?

My Access version is 2007

****************************
Private Sub ComboQ2D_AfterUpdate()
Dim mark As Variant
If Me![Plan] = "A" Then
mark = DLookup("ScoreA", "Assessment", "Description='" & [ComboQ2D] & "'")
Me![TextQ2S] = mark
Me!Totalmark = Nz([Q1S]) + Nz([Q2S]) + Nz([Q3S]) + Nz([Q4S]) + Nz([Q5S]) +
Nz([Q5nS]) + Nz([Q6S]) + ...
Else
mark = DLookup("ScoreB", "Assessment", "Description='" & [ComboQ2D] & "'")
Me![TextQ2S] = mark
Me!Totalmark = Nz([Q1S]) + Nz([Q2S]) + Nz([Q3S]) + Nz([Q4S]) + Nz([Q5S]) +
Nz([Q5nS]) + Nz([Q6S]) + ...
End If
End Sub
Click to expand...


You can put that code in a public Function procedure in the
form's module and the set all the control's AfterUpdate
event **property** to:
=thefunctionname()
instead of the usual [Event Procedure]

You can set all the contols' property in one shot by
selecting the controls by dragging a selection rectangle
around all of them or by holding down the Shift key and
clicking each control.

If your procedure needs to know the name of the control
object that triggered the event, you can get it by referring
to Me.ActiveControl

mark = DLookup("ScoreA", "Assessment", "Description='" _
& Me.ActiveControl.Name & "'")

If the controls were named with a common pattern such as
QxS, then you can use a loop to sum them:

total = 0
For x = 1 To 30
total = total + Nz(Me("Q" & x & "S"), 0)
Next x
Click to expand...
 
J

John W. Vinson

The form looks like a questionnaire with 30 questions
When the user change the answer, then the questionnaire re-calculcate the
marks again
Click to expand...

Are these answers stored in 30 fields in a table?

If so, what will you do when you add two more questions? Redesign your table,
add two new combos to your form, rewrite your code...!?

Consider using a normalized data structure with a one to many relationship
from a table of Questionnaires to a table of Answers, with thirty *ROWS* for
the questions rather than thirty *FIELDS*. You'll find it's a lot more
flexible and scalable. For one thing, you can put a textbox in the form footer
with a control source

=Sum([Answer])

to recalculate your total score automatically with no code at all.
Now i add AfterUpdate event to every combo

But it is not professional to duplicate 30 copies similar codes in the code
page.
Click to expand...

Change your code from a control's event procedure to a Function (not a sub) in
a stored Module:

Public Function CalcMark(frm As Form)
Dim mark As Variant
<do your calculation using frm!Comboboxname>
End Function

and call it from the afterupdate event:

=CalcMark(Form)
 
T

Tony WONG

Thanks a lot for complete solution

tony

John W. Vinson said:
The form looks like a questionnaire with 30 questions
When the user change the answer, then the questionnaire re-calculcate the
marks again
Click to expand...

Are these answers stored in 30 fields in a table?

If so, what will you do when you add two more questions? Redesign your
table,
add two new combos to your form, rewrite your code...!?

Consider using a normalized data structure with a one to many relationship
from a table of Questionnaires to a table of Answers, with thirty *ROWS*
for
the questions rather than thirty *FIELDS*. You'll find it's a lot more
flexible and scalable. For one thing, you can put a textbox in the form
footer
with a control source

=Sum([Answer])

to recalculate your total score automatically with no code at all.
Now i add AfterUpdate event to every combo

But it is not professional to duplicate 30 copies similar codes in the
code
page.
Click to expand...

Change your code from a control's event procedure to a Function (not a
sub) in
a stored Module:

Public Function CalcMark(frm As Form)
Dim mark As Variant
<do your calculation using frm!Comboboxname>
End Function

and call it from the afterupdate event:

=CalcMark(Form)
Click to expand...
 
Top