use query result to display a message

[Guest]

I use a Access Time Clock to tract employes hours. An employee enters his
EmId and clicks a command button to SignIn. The computer enters the date &
time. He does the same to SingOut. Sometimes someone forgets to SignOut. How
do I restrict the next SignIn before correcting the forgotten SignOut. thanks
JJF

 

[Michel Walsh]

Hi,



The latest operation, for a given employee, could not be of the same type
(ie, cannot be a sign in if you try a sign in). Since that implies a
cross-record validation (vertically). A standard table validation rule only
work horizontally, on one record, not across records. Jet-CHECK() constraint
allows vertical, across records, validations, as a rule, but MS SQL Server
would use trigger (since its CHECK constraint does not allow vertical,
across records, validations). If you are in a FORM, that is easier, look at
the records, for the given employee, and it is ok to sign in if:

DCount("*", "tableName", "employee=1100 AND SignInOut=-1) =
DCount("*", "tableName", "employee=1100 AND SignInOut=0")



ie, if the number of sign in = the number of sign out, for that employee.
The problem with table validation carried only at a form level is that entry
of data through another form, or another interface, may not do the
appropriate check and thus, data is in a less secure state than if the
validation is carried at a table design level.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks Mike I'll see where it leads ...JJF

Hi,



The latest operation, for a given employee, could not be of the same type
(ie, cannot be a sign in if you try a sign in). Since that implies a
cross-record validation (vertically). A standard table validation rule only
work horizontally, on one record, not across records. Jet-CHECK() constraint
allows vertical, across records, validations, as a rule, but MS SQL Server
would use trigger (since its CHECK constraint does not allow vertical,
across records, validations). If you are in a FORM, that is easier, look at
the records, for the given employee, and it is ok to sign in if:

DCount("*", "tableName", "employee=1100 AND SignInOut=-1) =
DCount("*", "tableName", "employee=1100 AND SignInOut=0")



ie, if the number of sign in = the number of sign out, for that employee.
The problem with table validation carried only at a form level is that entry
of data through another form, or another interface, may not do the
appropriate check and thus, data is in a less secure state than if the
validation is carried at a table design level.


Hoping it may help,
Vanderghast, Access MVP