Validation Rule and Calculating Average HELP

[Kristin]

1) I am creating a form for measurements of toothbrushes. For example
measuring the lenght of the bristles on the brush. There are specifications
that the bristle must fit into. I used the Validation Rule for the maximum
and minimum lenght of the bristle. However, I want the data to still be able
to be entered in. I basically just want a pop up saying that the measurment
is out of spec and then push the ok button and be able to keep that
measurment in there even though it is out of spec. Basically to point out
that the measurment is off.

2) There are 5 different measurments made on a bristle and the numbers will
be inputed individually on the form. I want the next line to calculate the
average automatically and store that like the other 5 measurements.

 

[Beetle]

1) I am creating a form for measurements of toothbrushes. For example

measuring the lenght of the bristles on the brush. There are specifications
that the bristle must fit into. I used the Validation Rule for the maximum
and minimum lenght of the bristle. However, I want the data to still be able
to be entered in. I basically just want a pop up saying that the measurment
is out of spec and then push the ok button and be able to keep that
measurment in there even though it is out of spec. Basically to point out
that the measurment is off.

Instead of using a validation rule in the table, you could use the Before
Update
event of each measurement control to check the value;

Private Sub MyControl_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

strMsg = "The value you entered is not within" & vbcrlf
strMsg = strMsg & "normal specs. Do you want to continue?"

If Me.MyControl < 2 Or Me.MyControl > 5 Then
If Msgbox (strMsg, vbYesNo, "Out Of Range") = vbNo Then
Cancel = True
Me.MyControl.Undo
End If
End If

End Sub

2) There are 5 different measurments made on a bristle and the numbers will
be inputed individually on the form. I want the next line to calculate the
average automatically and store that like the other 5 measurements.

Calculated values like this should not be stored in a table. They are just
calculated
as needed.

 

[NKTower]

I'll assume that
1) your 5 measurement fields are named
M1 through M5
2) the average field is named
MA
3) The data type for all in the table is NUMBER - SINGLE
4) The same validation rule is to be applied to all 5 measurements.
(If not, you will need to have multiple functions. I just have one function
named MCheck() defined here.)
5) The validation is that the measurement must be between 0.4 and 0.8 - see
code and change to real world values.

Then
a) For each field item M1 through M5 on your form, in the properties page, put
Validation Rule = MCheck([Mx])
where x is 1,2,3,4, or 5 as appropriate.

Then add this code behind the form:
' ------------------------------------
Option Compare Database
Option Explicit

Dim MX(5) As Control
Private Sub Form_Load()
Set MX(1) = Me.M1
Set MX(2) = Me.M2
Set MX(3) = Me.M3
Set MX(4) = Me.M4
Set MX(5) = Me.M5
End Sub

Private Function MCheck(ctl As Control)
If (ctl.Value < 0.4) Or (ctl.Value > 0.8) Then
MsgBox "Outside of range 0.4 - 0.8", vbOKOnly, "Warning"
End If
MCheck = -1 ' OK even if 'bad'
CalcAverage
End Function

Private Sub CalcAverage()
Dim sTotal As Single
Dim I As Integer
sTotal = 0
For I = 1 To 5
If IsNull(MX(I)) Then
Me.MA = Null ' too soon
Exit Sub
End If
sTotal = sTotal + MX(I)
Next I
MA = sTotal / 5
End Sub
' ------------------------