From: poorboy13 via AccessMonster.com on
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

From: Marshall Barton on
poorboy13 via AccessMonster.com wrote:
>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

--
Marsh
MVP [MS Access]
From: poorboy13 via AccessMonster.com on
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!


Marshall Barton wrote:
>>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
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

From: Marshall Barton on
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.


poorboy13 via AccessMonster.com wrote:
>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?
>
>
>Marshall Barton wrote:
>>>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
>>

--
Marsh
MVP [MS Access]
From: poorboy13 via AccessMonster.com on
The If IsNull did the trick! Thank you Thank you
I appreciate your willingness to share your knowledge!!

Thanks again,
Scott Pawling

Marshall Barton wrote:
>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]
>>> If DLookup("Present", "Attendance", "EmpID=" & something _
>>> & " And datefield= Date()") = 0 Then
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1