If a Value then Message Box To Terminate or Run another function

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

Guest

I would like my query to look at an employee table that has a value if the
employee is in or not. This query will clock an employee into a particular
job, but I want it to evaluate if the employee is in or not, if not in I want
the query to generate a message box that says he is not in, then either
terminate the process or be able to generate another macro or action.

EMP TABLE - Lists Employee and status
Append CLOCK IN QUERY looks at status and determines to End or Clock In

Thanks for any help.
 
Hi, Tony.

A query doesn't use procedural logic like "do step 1, then step 2 and if
step 2 brings up some condition, then do step 3." Queries use data set
logic, as in "gather info from the records in this defined data set." You'll
need VBA code to determine whether or not to take a specific action or set of
actions and then procede accordingly.

You could place a button on a form that, when clicked, will execute code
that runs through the decision tree. For example:

Private Sub RunQueryBtn_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If (DLookup("Status", "[EMP TABLE]", "Employee = '" & _
Me!txtEmpName.Value & "'") = "In") Then
CurrentDb().Execute "[CLOCK IN QUERY]", dbFailOnError
Else
ans = MsgBox("This employee is not in." & vbCrLf & _
"Would you like to do something else?", _
vbInformation + vbOKOnly, "Employee Not Available")
If (ans = vbYes) Then
' Do something else.
End If

End If

Exit Sub

ErrHandler:

MsgBox "Error in RunQueryBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where EMP TABLE is the name of the table where the employee's status
("In" or "Not In"), Status is the field to check, Employee is the field to
match the employee's name with, txtEmpName is the name of the text box
displaying the current employee's name, CLOCK IN QUERY is the name of the
query to execute if the employee is currently in.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top