Trouble recalculating field in sub-form

[FayeR]

I have eight fields in a continuous sub-form. When any of them changes, I
need to recalculate a 9th field (Average Rating). In addition, I want the
Average Rating to be calculated when the form appears.

I currently have the calculation in a module that is called from each of the
On Update events for the 8 fields. I have the Average Ratings field set up
as an unbound field. The module is pretty long.

I have a couple of issues - when one field in a record is updated all of the
Average Ratings change on ALL the records in the subform. When I attach the
Average Ratings field to a field in my table, I get an error message that You
can't Assign a value to this object.

I would prefer to store the calculated amount in my table. This database is
being used to test a web based system in development, so I am not that
concerned about normalizing my data. I need to use the calculated amounts on
another form later.

Is there a way for me to call that module when the subform is first
displayed and then call it again each time a change is made with the result
ending up in my table?

I apologize for the length of this post. I appreciate any help. Thanks!

 

[Maurice]

Why not put your calculations in a query and call the module from there. That
way you will obey the normalization and get the best of both worlds.
Continuous forms will always recalc every field.

hth

 

[FayeR]

Maurice,

Thanks for your help. This may be a little over my head. I'm not sure how
to call the module from a query. Do I change the module to use the field
names from the table - right now it has the control names? Do I requery
after I modify one of the fields in the form?

 

[Maurice]

Fayer,

Apologies for assuming you knew how to do this.

You module has to be a public one. In the querygrid place the following:

"average cost": modulename([field])

The modulename should be replaced with how you named your sub

for instance if you have public sub CalculateThis you should place the name
CalculateThis in the query. If your module uses variables you should place
those between the brackets.

I don't know exactly what your module does if possible post a peace of it so
we can take a look at it and help you further...

 

[FayeR]

Maurice,

Thanks for your quick response. I'll try to convert my module into a
function that can be called from the query.

Here's a piece of the current module. The case statements are repeated for
each field (Final Rating 1 thru Final Rating 8). I am currently using the
control names, but those would be changed to the parameters in the function.
The offending line has been commented out in the excerpt below.

Sub calctotals()
comptscore = 0
avgscore = 0
eecount = 0
If Not IsNull(Me.Final_Rating_1) Then
Select Case Me.Final_Rating_1
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If
If Not IsNull(Me.Final_Rating_2) Then
Select Case Me.Final_Rating_2
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If

-----Additional Case statements here

If eecount > 0 Then
avgscore = comptscore / eecount
'Me.EEScore.Value = avgscore

End If

End Sub

Fayer,

Apologies for assuming you knew how to do this.

You module has to be a public one. In the querygrid place the following:

"average cost": modulename([field])

The modulename should be replaced with how you named your sub

for instance if you have public sub CalculateThis you should place the name
CalculateThis in the query. If your module uses variables you should place
those between the brackets.

I don't know exactly what your module does if possible post a peace of it so
we can take a look at it and help you further...
--
Maurice Ausum

Maurice,

Thanks for your help. This may be a little over my head. I'm not sure how
to call the module from a query. Do I change the module to use the field
names from the table - right now it has the control names? Do I requery
after I modify one of the fields in the form?

 

[FayeR]

Maurice,

Thanks for all of your help!

I tried converting the module to a function with parameters. Here is my
call from the query:

AvgRating: CalcAvg([Final Rating 1],[Final Rating 2],[Final Rating 3],[Final
Rating 4],[Final Rating 5],[Final Rating 6],[Final Rating 7],[Final Rating 8])

I am getting the #Error in the query.

Here is an excerpt from the function:

Public Function calcavg(Rating1 As Integer, Rating2 As Integer, Rating3 As
Integer, Rating4 As Integer, Rating5 As Integer, Rating6 As Integer, Rating7
As Integer, Rating8 As Integer) As Double

comptscore = 0
avgscore = 0
eecount = 0
If Not IsNull(Rating_1) Then
Select Case Rating_1
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If
If Not IsNull(Rating_2) Then
Select Case Rating_2
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If

More Case statements for Ratings 3-8

If eecount > 0 Then
avgscore = comptscore / eecount
calcavg = avgscore
End If

End Function

Maurice,

Thanks for your quick response. I'll try to convert my module into a
function that can be called from the query.

Here's a piece of the current module. The case statements are repeated for
each field (Final Rating 1 thru Final Rating 8). I am currently using the
control names, but those would be changed to the parameters in the function.
The offending line has been commented out in the excerpt below.

Fayer,

Apologies for assuming you knew how to do this.

You module has to be a public one. In the querygrid place the following:

"average cost": modulename([field])

The modulename should be replaced with how you named your sub

for instance if you have public sub CalculateThis you should place the name
CalculateThis in the query. If your module uses variables you should place
those between the brackets.

I don't know exactly what your module does if possible post a peace of it so
we can take a look at it and help you further...
--
Maurice Ausum

Maurice,

Thanks for your help. This may be a little over my head. I'm not sure how
to call the module from a query. Do I change the module to use the field
names from the table - right now it has the control names? Do I requery
after I modify one of the fields in the form?


:

Why not put your calculations in a query and call the module from there. That
way you will obey the normalization and get the best of both worlds.
Continuous forms will always recalc every field.

hth
--
Maurice Ausum


:

I have eight fields in a continuous sub-form. When any of them changes, I
need to recalculate a 9th field (Average Rating). In addition, I want the
Average Rating to be calculated when the form appears.

I currently have the calculation in a module that is called from each of the
On Update events for the 8 fields. I have the Average Ratings field set up
as an unbound field. The module is pretty long.

I have a couple of issues - when one field in a record is updated all of the
Average Ratings change on ALL the records in the subform. When I attach the
Average Ratings field to a field in my table, I get an error message that You
can't Assign a value to this object.

I would prefer to store the calculated amount in my table. This database is
being used to test a web based system in development, so I am not that
concerned about normalizing my data. I need to use the calculated amounts on
another form later.

Is there a way for me to call that module when the subform is first
displayed and then call it again each time a change is made with the result
ending up in my table?

I apologize for the length of this post. I appreciate any help. Thanks!

 

[FayeR]

Maurice,

I was passing the wrong type of parameter (integer) instead of string to my
function. It is now working except in the case of parameters that are null.
I think I know how to handle that.

Thanks for your help!

Maurice,

Thanks for all of your help!

I tried converting the module to a function with parameters. Here is my
call from the query:

AvgRating: CalcAvg([Final Rating 1],[Final Rating 2],[Final Rating 3],[Final
Rating 4],[Final Rating 5],[Final Rating 6],[Final Rating 7],[Final Rating 8])

I am getting the #Error in the query.

Here is an excerpt from the function:

Public Function calcavg(Rating1 As Integer, Rating2 As Integer, Rating3 As
Integer, Rating4 As Integer, Rating5 As Integer, Rating6 As Integer, Rating7
As Integer, Rating8 As Integer) As Double

comptscore = 0
avgscore = 0
eecount = 0
If Not IsNull(Rating_1) Then
Select Case Rating_1
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If
If Not IsNull(Rating_2) Then
Select Case Rating_2
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If

More Case statements for Ratings 3-8

If eecount > 0 Then
avgscore = comptscore / eecount
calcavg = avgscore
End If

End Function

Maurice,

Thanks for your quick response. I'll try to convert my module into a
function that can be called from the query.

Here's a piece of the current module. The case statements are repeated for
each field (Final Rating 1 thru Final Rating 8). I am currently using the
control names, but those would be changed to the parameters in the function.
The offending line has been commented out in the excerpt below.

Fayer,

Apologies for assuming you knew how to do this.

You module has to be a public one. In the querygrid place the following:

"average cost": modulename([field])

The modulename should be replaced with how you named your sub

for instance if you have public sub CalculateThis you should place the name
CalculateThis in the query. If your module uses variables you should place
those between the brackets.

I don't know exactly what your module does if possible post a peace of it so
we can take a look at it and help you further...
--
Maurice Ausum


:

Maurice,

Thanks for your help. This may be a little over my head. I'm not sure how
to call the module from a query. Do I change the module to use the field
names from the table - right now it has the control names? Do I requery
after I modify one of the fields in the form?


:

Why not put your calculations in a query and call the module from there. That
way you will obey the normalization and get the best of both worlds.
Continuous forms will always recalc every field.

hth
--
Maurice Ausum


:

I have eight fields in a continuous sub-form. When any of them changes, I
need to recalculate a 9th field (Average Rating). In addition, I want the
Average Rating to be calculated when the form appears.

I currently have the calculation in a module that is called from each of the
On Update events for the 8 fields. I have the Average Ratings field set up
as an unbound field. The module is pretty long.

I have a couple of issues - when one field in a record is updated all of the
Average Ratings change on ALL the records in the subform. When I attach the
Average Ratings field to a field in my table, I get an error message that You
can't Assign a value to this object.

I would prefer to store the calculated amount in my table. This database is
being used to test a web based system in development, so I am not that
concerned about normalizing my data. I need to use the calculated amounts on
another form later.

Is there a way for me to call that module when the subform is first
displayed and then call it again each time a change is made with the result
ending up in my table?

I apologize for the length of this post. I appreciate any help. Thanks!

 

[FayeR]

Success!! It's working perfect after the change in the parameter type and
also eliminating null parameters.

Thanks for sending me in the right direction.

Maurice,

I was passing the wrong type of parameter (integer) instead of string to my
function. It is now working except in the case of parameters that are null.
I think I know how to handle that.

Thanks for your help!

Maurice,

Thanks for all of your help!

I tried converting the module to a function with parameters. Here is my
call from the query:

AvgRating: CalcAvg([Final Rating 1],[Final Rating 2],[Final Rating 3],[Final
Rating 4],[Final Rating 5],[Final Rating 6],[Final Rating 7],[Final Rating 8])

I am getting the #Error in the query.

Here is an excerpt from the function:

Public Function calcavg(Rating1 As Integer, Rating2 As Integer, Rating3 As
Integer, Rating4 As Integer, Rating5 As Integer, Rating6 As Integer, Rating7
As Integer, Rating8 As Integer) As Double

comptscore = 0
avgscore = 0
eecount = 0
If Not IsNull(Rating_1) Then
Select Case Rating_1
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If
If Not IsNull(Rating_2) Then
Select Case Rating_2
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If

More Case statements for Ratings 3-8

If eecount > 0 Then
avgscore = comptscore / eecount
calcavg = avgscore
End If

End Function

Maurice,

Thanks for your quick response. I'll try to convert my module into a
function that can be called from the query.

Here's a piece of the current module. The case statements are repeated for
each field (Final Rating 1 thru Final Rating 8). I am currently using the
control names, but those would be changed to the parameters in the function.
The offending line has been commented out in the excerpt below.




:

Fayer,

Apologies for assuming you knew how to do this.

You module has to be a public one. In the querygrid place the following:

"average cost": modulename([field])

The modulename should be replaced with how you named your sub

for instance if you have public sub CalculateThis you should place the name
CalculateThis in the query. If your module uses variables you should place
those between the brackets.

I don't know exactly what your module does if possible post a peace of it so
we can take a look at it and help you further...
--
Maurice Ausum


:

Maurice,

Thanks for your help. This may be a little over my head. I'm not sure how
to call the module from a query. Do I change the module to use the field
names from the table - right now it has the control names? Do I requery
after I modify one of the fields in the form?


:

Why not put your calculations in a query and call the module from there. That
way you will obey the normalization and get the best of both worlds.
Continuous forms will always recalc every field.

hth
--
Maurice Ausum


:

I have eight fields in a continuous sub-form. When any of them changes, I
need to recalculate a 9th field (Average Rating). In addition, I want the
Average Rating to be calculated when the form appears.

I currently have the calculation in a module that is called from each of the
On Update events for the 8 fields. I have the Average Ratings field set up
as an unbound field. The module is pretty long.

I have a couple of issues - when one field in a record is updated all of the
Average Ratings change on ALL the records in the subform. When I attach the
Average Ratings field to a field in my table, I get an error message that You
can't Assign a value to this object.

I would prefer to store the calculated amount in my table. This database is
being used to test a web based system in development, so I am not that
concerned about normalizing my data. I need to use the calculated amounts on
another form later.

Is there a way for me to call that module when the subform is first
displayed and then call it again each time a change is made with the result
ending up in my table?

I apologize for the length of this post. I appreciate any help. Thanks!

 

[Maurice]

Hey you're welcome

just a little push can change the mind ;-)
--
Maurice Ausum

Success!! It's working perfect after the change in the parameter type and
also eliminating null parameters.

Thanks for sending me in the right direction.

Maurice,

I was passing the wrong type of parameter (integer) instead of string to my
function. It is now working except in the case of parameters that are null.
I think I know how to handle that.

Thanks for your help!

Maurice,

Thanks for all of your help!

I tried converting the module to a function with parameters. Here is my
call from the query:

AvgRating: CalcAvg([Final Rating 1],[Final Rating 2],[Final Rating 3],[Final
Rating 4],[Final Rating 5],[Final Rating 6],[Final Rating 7],[Final Rating 8])

I am getting the #Error in the query.

Here is an excerpt from the function:

Public Function calcavg(Rating1 As Integer, Rating2 As Integer, Rating3 As
Integer, Rating4 As Integer, Rating5 As Integer, Rating6 As Integer, Rating7
As Integer, Rating8 As Integer) As Double

comptscore = 0
avgscore = 0
eecount = 0
If Not IsNull(Rating_1) Then
Select Case Rating_1
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If
If Not IsNull(Rating_2) Then
Select Case Rating_2
Case "Learning"
comptscore = comptscore + 1
eecount = eecount + 1
Case "Exhibiting"
comptscore = comptscore + 2
eecount = eecount + 1
Case "Demonstrating"
comptscore = comptscore + 3
eecount = eecount + 1
Case "Modeling"
comptscore = comptscore + 4
eecount = eecount + 1
End Select
End If

More Case statements for Ratings 3-8

If eecount > 0 Then
avgscore = comptscore / eecount
calcavg = avgscore
End If

End Function



:

Maurice,

Thanks for your quick response. I'll try to convert my module into a
function that can be called from the query.

Here's a piece of the current module. The case statements are repeated for
each field (Final Rating 1 thru Final Rating 8). I am currently using the
control names, but those would be changed to the parameters in the function.
The offending line has been commented out in the excerpt below.




:

Fayer,

Apologies for assuming you knew how to do this.

You module has to be a public one. In the querygrid place the following:

"average cost": modulename([field])

The modulename should be replaced with how you named your sub

for instance if you have public sub CalculateThis you should place the name
CalculateThis in the query. If your module uses variables you should place
those between the brackets.

I don't know exactly what your module does if possible post a peace of it so
we can take a look at it and help you further...
--
Maurice Ausum


:

Maurice,

Thanks for your help. This may be a little over my head. I'm not sure how
to call the module from a query. Do I change the module to use the field
names from the table - right now it has the control names? Do I requery
after I modify one of the fields in the form?


:

Why not put your calculations in a query and call the module from there. That
way you will obey the normalization and get the best of both worlds.
Continuous forms will always recalc every field.

hth
--
Maurice Ausum


:

I have eight fields in a continuous sub-form. When any of them changes, I
need to recalculate a 9th field (Average Rating). In addition, I want the
Average Rating to be calculated when the form appears.

I currently have the calculation in a module that is called from each of the
On Update events for the 8 fields. I have the Average Ratings field set up
as an unbound field. The module is pretty long.

I have a couple of issues - when one field in a record is updated all of the
Average Ratings change on ALL the records in the subform. When I attach the
Average Ratings field to a field in my table, I get an error message that You
can't Assign a value to this object.

I would prefer to store the calculated amount in my table. This database is
being used to test a web based system in development, so I am not that
concerned about normalizing my data. I need to use the calculated amounts on
another form later.

Is there a way for me to call that module when the subform is first
displayed and then call it again each time a change is made with the result
ending up in my table?

I apologize for the length of this post. I appreciate any help. Thanks!