The original post now makes sense, especially that you mention that you've
been working with Excel.
You're essentially looking at validation which is confirming the state of a
value or a record before or after an update.
Within Access, there are events that fire for the Form object as well as the
control objects ( a list box, a drop down, a text field, etc.) All of the
form & control objects have a _BeforeUpdate and a _AfterUpdate event which
fires (obviously) before changes are committed to the table and afterward.
The _BeforeUpdate event allows you to intercept the event and if needbe
cancel the action. However, given your scenario you can setup the drop down
list to show either 'ALL EMPLOYEES', 'EMPLOYEES NOT CHECKED IN', or
'EMPLOYEES NOT CHECKED OUT'. The .RecordSet property of the drop down list
is a SQL statement, table name or query that pulls the relevant data and
populates the list. Sample SQL statements are below...
ALL Employees
SELECT FirstName, LastName FROM Employees ORDER BY LastName, FirstName
Employees Not Checked In
SELECT FirstName, LastName FROM Employees WHERE IsNull(CheckIn) ORDER BY
LastName, FirstName
Employees Not Checked Out
SELECT FirstName, LastName FROM Employees WHERE IsNull(CheckOut) ORDER BY
LastName, FirstName
If the check in/check out information is in a separate table, obviously the
SQL statements would have to be modified to include that table. (Post back
if you need help on that).
Obviously, restricting the options in the drop down list somewhat eliminates
the need to validate the selection since you've already effectively done
that. However, if you DO need to validate the selection, you'll use code
similar to this...
Private Sub cboDispatchStatus_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_cboDispatchStatus_BeforeUpdate
Dim strMsgText As String
strMsgText = ""
If Me.txtLoadStatus <> "Ready for Dispatch" Then
strMsgText = "This trailer cannot be dispatched until the load
status is updated to 'Ready for Dispatch.'"
MsgBox strMsgText, vbInformation + vbOKOnly
Cancel = True
Exit Sub
End If
If IsNull(Me.cboDispatchLocation) = True Then
strMsgText = strMsgText & "-The trailer dispatch location must be
selected first, before the status can be updated to 'Dispatched'." & Chr(13)
Me.Undo
MsgBox strMsgText, vbInformation + vbOKOnly
Cancel = True
End If
Exit_cboDispatchStatus_BeforeUpdate:
Exit Sub
Err_cboDispatchStatus_BeforeUpdate:
MsgBox getDefaultErrorMessage(Me.Name, "cboDispatchStatus_BeforeUpdate",
Err.Number), vbCritical
Resume Exit_cboDispatchStatus_BeforeUpdate
End Sub
In this example, there are two conditions that if either or both are true
will CANCEL the update by simply setting CANCEL = TRUE.
In your scenario, you would most likely use a DLOOKUP() to pull the
information from the relevant table and then check it as in...
EmployeeCheckIn = DLookup("CheckIn", "EmployeeStatus", "Id = '" &
cboEmployeeDropDownList & "' AND Date=Date())
EmployeeCheckOut = DLookup("CheckOut", "EmployeeStatus", "Id = '" &
cboEmployeeDropDownList & "' AND Date=Date() AND IsNull("CheckIn") = False")
Note: When you declare the variables that will hold the result of the
DLOOKUPS declare them as variants as 'Null' is a valid result from a
Dlookup()
Dim EmployeeCheckIn as Variant
Dim Employee CheckOut as Variant
If the employee has NOT checked in, the DLookup() will return NULL. Likewise
an employee that has not checked out (but has checked in) will be null as
well. As the DLookup()'s are written, they are only looking at today's date
meaning that there could be an employee record from an earlier date where
the employee checked in, but has not checked out. While this is entirely
possible for employees who work graveyard 11:00 PM - 6:00 PM, it does allow
for situations where an employee from three days ago left without checking
out. You'll need to decide how to handle *both* scenarios.
Once you have the DLookup's working then its just a matter of tweaking the
code as in
If Not IsNull(EmployeeCheckIn) then
MsgBox ("Employee has already checked in. Check in date & time:"
& EmployeeCheckIn
Cancel = True
end if
The *NOT* is neccessary because if the DLookup for EmployeeCheckIn returns a
value, the variable will NOT be Null (Empty). The same scenario applies to
the EmployeeCheckOut.
You will have to a means by which you indicate that the user is in
'Check-In' mode versus 'Check-Out' mode as the logic for validating the
record will be different.
