Validate field based on another calculated field

[LEXCERM]

Hi all,

Form has several fields where numeric data is input.
A separate calculated field continually adds these figures together.

If the calculated field has a value of less than 0, I want a message to appear in the active field stating this and then undo the entry in that field.

I've tried code in the before update and change events, but nothing works.

Help!!!

Merry xmas in advance, btw! ;-)

Paul.

 

[RuralGuy]

Show us what you have tried in the BeforeUpdate event of the control?

 

[LEXCERM]

I've been trying something like this:-

If Me.CalculatedField < 0 Then
Forms![FRM_ENTRY]![subfrm_Entry]![CurrentField].Undo
...or ...
Me.CurrentField.Setfocus
Me.CurrentField.Undo
End If

 

[RuralGuy]

Way too much redundant code to start off with. Is this just one control that is displaying the message or are wanting several controls to work the same way? Is this control involved in the Sum() you are keying off of?

 

[LEXCERM]

Btw, I forgot to say "thanks for replying" so thank you.

I need six other controls to work the same way and they are all involved in the Sum-off.

Thanks again,
P.

 

[RuralGuy]

You will have a problem because the Control.Value is not "really" updated until the AfterUpdate event. The Control.Text value *is* valid for this use but requires the control to have the focus in order to access it. To get all of the controls to essentially work the same I recommend creating a Function in your form class (code) module that does the sum and returns True/False depending on the results <0. It also avoids any timing issues with the Access tasker that needs to run to do the Sum() as you have it currently set up. Let me think on it a bit and I'll come up with the Function and some psuedo code that uses dummy names but gives you the idea of what to do. The BeforeUpdate event is really the best for this since it can be cancelled and leave the focus in the same control.

 

[LEXCERM]

Thanks for looking into this for me.

Many thanks,
p.

 

[RuralGuy]

This is << AIR CODE >> so be warned. Let me know if you need further explainations for this.

Private Function SumIt() As Boolean

'-- Sum 6 controls and return True if <0
'-- Dummied with ControlNames of Val1, Val2, Val3, Val4, Val5, Val6
'-- We do not know what control we are operating from

   Dim txtControl As Control
   Dim CtlWithFocus As Control

   Dim MySum As Double
   Dim x As Integer

   Set CtlWithFocus = Me.ActiveControl         ' Set the control for return focus

   For x = 1 To 6
      Set txtControl = Me.Controls("Val" & x)
      txtControl.SetFocus
      MySum = MySum + Nz(txtControl.Text, 0)
   Loop
   SumIt = MySum < 0
   CtlWithFocus.SetFocus         '-- Return the focus to the original control
   Set CtlWithFocus = Nothing    '-- Release the Set Variables
   Set txtControl = Nothing      '-- ""

End Function

Private Sub Val1_BeforeUpdate(Cancel As Integer)
'-- Example of BeforeUpdate event of *one* of the controls.

   If SumIt Then
      Cancel = True
      Me.Val1.Undo
   End If

End Sub

 

[LEXCERM]

Sorry for not replying sooner. I tried your code and it worked to a point, but couldn't get it to work 100%. However, I saw one small line of code which did the trick:-
Cancel = True.

I revised the BeforeUpdate event to this and the text boxes now work accordingly:-

If Me.CalculatedField < 0 Then
Cancel = True
Me.NameOfActiveControl.Undo
End If


Many thanks again for your time,
Paul.

 

[RuralGuy]

Great! Glad we could help.