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

[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.

 

[PieterLinden via AccessMonster.com]

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.

Why are you doing the validation in a button click? Why not in the
BeforeUpdate of the control you are entering data into?

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

Oh, I get it now... You're attempting to look in a table to return the value
of "remaining Vacation Sick Time" and you can't do that like this. I don't
know what your Remaining Sick Time query looks like... but you could probably
do this with either DSUM(), DCount() or using a function to return the number
of vacation Hours left

 

[GoBrowns!]

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!!!!!!!!!!!!