Validate Two Text Boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Time has run out and I need to have an Access db completed in a couple hours
but am having a problem with a validation issue. If anyone has code, it would
be greatly appreciated. I have two text boxes - Max Vol and Min Vol. Both
have to be more than 0 and, of course, Min has to be less than Max. I have
criteria in the Properties boxes but this isn't working quite right. If one
enters 1 in the Max box and then 0 in the Min, an error message is displayed
but I can't get it to cancel the Min Vol and move back to Max.

Thanks in advance for any assistance you can give me! Jani
 
Validation code that applies to more than one field or that checks for null
values, must be placed in the FORM's BeforeUpdate event. If an error is
found, you would cancel the event and set focus to the field in error.

If Me.MinVol > 0 then
If Me.MaxVol > Me.MinVol Then
Else
MsgBox "MaxVol must be > MinVol", vbOkOnly
Cancel = True
Me.MaxVol.SetFocus
End If
Else
MsgBox "MinVol must be > 0", vbOKOnly
Cancel = True
Me.MinVol.SetFocus
End If

I know this code doesn't check all the conditions but it does check the
relevant ones and it checks them in a useful order. If you want to have a
separate error message for when MaxVol isn't greater than zero, you'll need
to change the code.
 
Thanks for the quick response... however, I already have the code below in
the Form BeforeUpdate event and I don't know how to incorporate your code
within it. Tried a few different ways but have been unsuccessful.

Jani

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
Msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is
provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next

End Sub
 
Add it either before or after the For loop.

Jani said:
Thanks for the quick response... however, I already have the code below in
the Form BeforeUpdate event and I don't know how to incorporate your code
within it. Tried a few different ways but have been unsuccessful.

Jani

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
Msg = "Data Required for '" & ctl.Name & "' field!" & nl &
_
"You can't save this record until this data is
provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next

End Sub
Click to expand...
 
Pat - or someone, thanks again for responding. I am such a novice that I have
not been successful in getting this code to work! If you have time, would
appreciate more explanation. that old timeline keeps inching backward!
Thanks, Jani
 
Back
Top