Restricting data entered in form

[tMitch]

Hi -

I want to put a restriction on data entered in a 1st subform (“sfr2”), but don’t know how to write the code. Want I want to say is: If sfr2.cboBox = 6, then sfr2.QtyApproved cannot be greater than sfr1.FinalQty.

I tried as an After Update Event:

If Me.sfr2.cboBox = 6 then
Me.sfr2.QtyApproved <= Me.Parent.sfr1.FinalQty

But it didn’t work. I also tried it as a Validation Rule, but it didn't work either (didn't seem to like the If). Any suggestions?

Thanks.

 

[Fornatian]

Use the BeforeUpdate event to apply validation, the afterupdate event fires once the value has been saved.

If Me.sfr2.cboBox = 6 Then
Msgbox "Quantity Approved must be less than " & Me.Parent.sfr1.FinalQty +1,vbExcalmation,"Error"
Me.sfr2.QtyApproved.SetFocus
End if

 

[tMitch]

Thank you. I tried what you suggested, but am now I'm getting an error message that says "you must save the field before you execute the SetFocus".

 

[Fornatian]

try:

If Me.sfr2.cboBox = 6 Then
If Me.sfr2.QtyApproved > Me.Parent.sfr1.FinalQty Then
Cancel = True
Msgbox "Quantity Approved must be less than " & Me.Parent.sfr1.FinalQty +1,vbExcalmation,"Error"
Me.sfr2.QtyApproved.SetFocus
End if
End if

 

[tMitch]

Thank you - that worked. However, when I enter a number greater than the Final Qty, I still get the message that I must save the field before executing the GotoControl or SetFocus method after my message that says "Quantity approved must be less than...". It's not a big deal, I just click End, but is there a way to get rid of that message?

 

[Fornatian]

is your before update event in the main form or subform, it should be in the subform before update event, I tried this code and it worked:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent.F1 > F3 Then
Cancel = True
MsgBox "Quantity Approved must be less than " & Me.Parent.F1, , "Error"
Me.F3.SetFocus
End If
End Sub

where F1 is a field in the main form and F3 is a field in the subform.

 

[tMitch]

Yes, that worked. Thank you.