Descision based on a query

[poorboy13 via AccessMonster.com]

Hello,
I'm hoping this should be easy - I just can't seem to find the answer. I'm
guessing my syntax is wrong but I can not figure it out.
I am making a Db for work to keep track of Tasks and Performance.
The 2 main functions I am trying to keep track of is total hours signed in
and how long it takes to complete each of the tasks assigned to the Employee.

I'm trying to put a small check in it that an employee can not begin a task
without being signed in for the day. This is being tracked on 2 different
tables. (Attendance.tbl and Times.tbl)
So in the form to begin the task when you click on the command button I want
it to run a query, in the background, of the attendance table to see if the
employee is present (Yes/No Check Box).

If No it will bring up a MsgBox to tell him he has not signed in yet and then
cancel the operation and not put anything in the Times table.
If yes then it will go through and complete the form with the date and time
it was started.
I have it so that it runs the query, not in the back ground but at least it
runs,
The Problem is regardless of whether the employee is signed in or not I still
get the message that he is not signed in.

Here is the code so far:
Private Sub Command14_Click()
DoCmd.OpenQuery ("qry_GetWork")
If Present = 0 Then
MsgBox "Please Sign in to begin working!", vbOKOnly, "Can Not Continue"
DoCmd.Close 'To Close the Query Window
DoCmd.Close 'To Close the Form

Else

StartDate.Locked = False
StartTime.Locked = False
StartDate = Date
StartTime = Now()
StartTime.Locked = True
StartDate.Locked = True
DoCmd.Close 'Close Form
End If
End Sub

I can live with the query window showing if the thing would get the right
answer.
Or maybe there is a completely different way I should attack it????

Thanks, in Advance!

 

[Marshall Barton]

I'm hoping this should be easy - I just can't seem to find the answer. I'm
guessing my syntax is wrong but I can not figure it out.
I am making a Db for work to keep track of Tasks and Performance.
The 2 main functions I am trying to keep track of is total hours signed in
and how long it takes to complete each of the tasks assigned to the Employee.

I'm trying to put a small check in it that an employee can not begin a task
without being signed in for the day. This is being tracked on 2 different
tables. (Attendance.tbl and Times.tbl)
So in the form to begin the task when you click on the command button I want
it to run a query, in the background, of the attendance table to see if the
employee is present (Yes/No Check Box).

If No it will bring up a MsgBox to tell him he has not signed in yet and then
cancel the operation and not put anything in the Times table.
If yes then it will go through and complete the form with the date and time
it was started.
I have it so that it runs the query, not in the back ground but at least it
runs,
The Problem is regardless of whether the employee is signed in or not I still
get the message that he is not signed in.

Here is the code so far:
Private Sub Command14_Click()
DoCmd.OpenQuery ("qry_GetWork")
If Present = 0 Then
MsgBox "Please Sign in to begin working!", vbOKOnly, "Can Not Continue"
DoCmd.Close 'To Close the Query Window
DoCmd.Close 'To Close the Form

Else

StartDate.Locked = False
StartTime.Locked = False
StartDate = Date
StartTime = Now()
StartTime.Locked = True
StartDate.Locked = True
DoCmd.Close 'Close Form
End If
End Sub

I can live with the query window showing if the thing would get the right
answer.
Or maybe there is a completely different way I should attack it????

Opening a query's datasheet does not make the query's data
available to your VBA code.

In your case, I think you want to use the DLookup function.
How you would do that depends on what you have in the query,
probably something vaguely like:
If DLookup("Present", "Attendance", "EmpID=" & something _
& " And datefield= Date()") = 0 Then

 

[poorboy13 via AccessMonster.com]

I appreciate the response - However, I have tried every which way I can think
of to write it and I'm not coming up with a correct answer.

To Clarify;
In the query "qry_GetWork" I have specified the criteria for "ShiftDate =
Date()" and "EmpID = [Forms]![Begin]![Employee ID]". (This, by itself, works
fine.)

So as best I can tell the If statement in VBA should look like this:
If DLookup("Present", "qry_GetWork") <> -1 Then

Now I get the opposite problem everything is going through like the box is
checked. Even if it is not, and even if there are no records at all in the
query.

I did the "<>-1" because if I have not signed in yet then the query will not
return any records.
Is that wrong?

Thanks again!

I'm hoping this should be easy - I just can't seem to find the answer. I'm
guessing my syntax is wrong but I can not figure it out.

[quoted text clipped - 41 lines]

answer.
Or maybe there is a completely different way I should attack it????

Opening a query's datasheet does not make the query's data
available to your VBA code.

In your case, I think you want to use the DLookup function.
How you would do that depends on what you have in the query,
probably something vaguely like:
If DLookup("Present", "Attendance", "EmpID=" & something _
& " And datefield= Date()") = 0 Then

 

[Marshall Barton]

If the query does not return any records then the DLookup
will faill to find anything so the result will be Null
making the If statement fail. If someone is checking in,
there won't be a record yet, right? On this case you should
check for Null:
If IsNull(DLookup("Present", "qry_GetWork")) Then

Are you sure the Present field in its table is a Yes/No
field? If it is, double check the value of the field is
either 0 or -1 (eg. SQL Server uses 0 and +1 instead of
Access's 0 and -1).

If none of that helps, try my earlier suggestion skip the
the query and have DLookup go directly to the table.

I appreciate the response - However, I have tried every which way I can think
of to write it and I'm not coming up with a correct answer.

To Clarify;
In the query "qry_GetWork" I have specified the criteria for "ShiftDate =
Date()" and "EmpID = [Forms]![Begin]![Employee ID]". (This, by itself, works
fine.)

So as best I can tell the If statement in VBA should look like this:
If DLookup("Present", "qry_GetWork") <> -1 Then

Now I get the opposite problem everything is going through like the box is
checked. Even if it is not, and even if there are no records at all in the
query.

I did the "<>-1" because if I have not signed in yet then the query will not
return any records.
Is that wrong?

I'm hoping this should be easy - I just can't seem to find the answer. I'm
guessing my syntax is wrong but I can not figure it out.

[quoted text clipped - 41 lines]

answer.
Or maybe there is a completely different way I should attack it????

Opening a query's datasheet does not make the query's data
available to your VBA code.

In your case, I think you want to use the DLookup function.
How you would do that depends on what you have in the query,
probably something vaguely like:
If DLookup("Present", "Attendance", "EmpID=" & something _
& " And datefield= Date()") = 0 Then

 

[poorboy13 via AccessMonster.com]

The If IsNull did the trick! Thank you Thank you
I appreciate your willingness to share your knowledge!!

Thanks again,
Scott Pawling

If the query does not return any records then the DLookup
will faill to find anything so the result will be Null
making the If statement fail. If someone is checking in,
there won't be a record yet, right? On this case you should
check for Null:
If IsNull(DLookup("Present", "qry_GetWork")) Then

Are you sure the Present field in its table is a Yes/No
field? If it is, double check the value of the field is
either 0 or -1 (eg. SQL Server uses 0 and +1 instead of
Access's 0 and -1).

If none of that helps, try my earlier suggestion skip the
the query and have DLookup go directly to the table.

I appreciate the response - However, I have tried every which way I can think
of to write it and I'm not coming up with a correct answer.

[quoted text clipped - 29 lines]