calculating text box values in a form

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi all,

I am trying to update a database that a previous person
has set up and am running into a problem adding fields in
a form. On the form, I have ten text boxes that I would
like to add up and fill a Totals box. I have the
following code in the first text box:


Private Sub IQ1_Score_AfterUpdate()
On Error GoTo Err_IQ1_Score_AfterUpdate

If Me.IQ1_Score > 10 Or Me.IQ1_Score < 0 Then
MsgBox "Value should be between 1 and 10.",
vbExclamation, "Notice:"
Me.IQ1_Score.SetFocus
Else

'Recalculates all ten fields and updates the "Total"

'Me.Total_Case_Score = "(Me.IQ1_Score) +
(Me.IQ2a_Score) + (Me.IQ2b_Score) + (Me.IQ2c_Score) +
(Me.IQ3a_Score) + (Me.IQ3b_Score) + (Me.IQ3c_Score) +
(Me.IQ3d_Score) + (Me.IQ4_Score) + (Me.IQ5_Score)"

End If


Exit_IQ1_Score_AfterUpdate:
Exit Sub

Err_IQ1_Score_AfterUpdate:
MsgBox Err.Description
Resume Exit_IQ1_Score_AfterUpdate

End Sub


I have the following code in the other 9 text boxes:

Call IQ1_Score_AfterUpdate

My problem: when I run the form and go to enter a number
in a field I get a error message saying, "The value you
entered isn't valid for this field."

I am not sure where the problem is? The form's control
source is the underlying table... not sure where to start
looking to solve the problem.

Thanks!!!!!!!
 
The first step is to look at the data source for the form
(properties). This will give you the table or query name
that is supplying the form with data.

Check the Tables and Queries within the database to find
the source and check the table design or query design to
see what data types have been allocated to the fields.
 
The form is based off of one table and the 10 scores along
with the total is a "number" type. Nothing seems to be
out of order here. Any other suggestions?

Thanks!!!
 
You have unnecessary single and double quotes in your
assignment expression, and field names should be in
brackets rather than parentheses, although Access may
supply the missing brackets. Moreover, though, you
needn't assign the value using AfterUpdate procedures;
just set the control's Control Source to your expression:


=[Me.IQ2a_Score] + [Me.IQ2b_Score] + [Me.IQ2c_Score] +
[Me.IQ3a_Score] + [Me.IQ3b_Score] + [Me.IQ3c_Score] +
[Me.IQ3d_Score] + [Me.IQ4_Score] + [Me.IQ5_Score]

HTH
Kevin Sprinkel
 
Back
Top