Validate between form text box and query field in a form?

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
G

GoBrowns!

I am trying to create a button that will let the user validate that the
information they are entering does not violate any rules before they submit
it.

I want the button to take the amount of Vacation Time Used (a text field in
the form), and subtract that from the amount of Vacation the employee has
left to use (a calculated field in a query). If the amount of Vacation Time
Used is more than the Vacation Time remaining to use, I want a message box to
pop up that alerts the user of the error.

Here is my code:

Private Sub cmdValidate_Click()
'Validates that any entries in the Vacation and Sick Time
'buckets conform to the validation rules
Dim VacationError As String
Dim Validated As String
RemainingVacation = DoCmd.OpenQuery("qryRemainingVacationSickTime",
acViewNormal, acReadOnly)
If (RemainingVacation - txtVacationUsed < 0) Then
VacationError = MsgBox("Vacation Time Used exceeds Vacation Time
Remaining." & vbLf & _
"Reevaluate before submitting.", _
vbOKOnly, "Validation Error", vbCritical)
Else
Validated = MsgBox("Validated and ready for submission.", vbOKOnly,
"Validation Complete", _
, vbExclamation)
End If
End Sub

What is wrong with the code that is making it not work? I am new to VBA and
STUMPED.
 
My Remaining Sick Time Query takes all of the records that were entered using
the form in question and tallies how many vacation and sick hours the
employee has used so far.

How would I use DSUM or DCOUNT? Where would that go - in the code for my
"Validate" button or someplace else?

Would it be easier for me to add the Remaining Sick Time field from my query
to the form, hide it, as use that to do the calculation? If so, how would I
do all of that?

Thanks for the help!!!!!!!!!!!!
 
Back
Top