Access the value of checkbox in order to open the form

[Sandy1992]

Hi guys,

Uptil now I got plenty of helps from this forum. I really appreciate your wonderful help. Thank you so much.

I am stuck at one point, my database looks like this.
Table: Tasks(EmployeeI d,StartTime,End Time,Duration,C heckEndTime)
Query:TasksQuer y(EmployeeId,En dTime,Duration, CheckEndTime)

Please Note: Her CheckEndTime is nothing but Checkbox field.

Form : Form1

Button: Command17

Now, the flow is like this. When the user have not entered EndTime(i.e. Only starttime has been entered), then the Checkbox will remain unchecked.
And it will move to TasksQuery.

So, When the user clicks on "Command17" the query should be checked to see if the logged in user is present there, if yes than message should be displayed, else the form should be opened.

I tried several logics, but it seems it doesnot work well with checkbox field. It is always display null or 0 value depending upon different logic's.

(To add my checkbox is a 2 state checkbox)

What can I do to check if the field is checked or the employeeid is present in the Query or not.

Also, the query should get refreshed.
Any help will be appreciated.

Please advice.

[TheSmileyCoder]

Question, why are you having a checkbox? It seems to be redundant information, as you could simply check if the endtime is filled out.

If IsNull(me.EndTime) then
  'Endtime is missing
Else
  'Endtime is entered.
end if

[Sandy1992]

Hi Smiley,

Actually the reason behind having this redundancy is, when I tried checking the checkbox in the table if it is checked or not, it was simply displaying "True" incase one checkbox with the logged in user employeeid is checked. It was not checking the full table. So, I thought to create query so it have to only check for one time. But now it is not even doing that.

Also, apologies but your code will not work, as I need to compare the Logged in EmployeeId field.

If DCount("Duration", "Tasks", "End Is Null And EmployeeId = " & Me.txtUserID) >0 Then

     MsgBox "You can't add another task because you haven't finished the last one."
Else
    DoCmd.OpenForm "Tasks"
    MsgBox "Open form"
End If
On Error GoTo Err
MsgBox Error$

But problem which I faced in this code is, it always displays null value. There is something wrong in Criteria part. Please suggest if I am going wrong somewhere.

[TheSmileyCoder]

Okay, the code you posted looks like it should work, but since it is not, there are some things we could look into

DCount("Duration", "Tasks", "End Is Null And EmployeeId = " & Me.txtUserID) >0

First, End is a reserved word in Access, and using reserved words can sometimes cause troubles, and as such it is best to avoid them. Especially since when and where they cause issues is not something I fully understand.

For starters, to see if it could be related to the reserved words issue, you could try to use the following syntax:

DCount("Duration", "Tasks", "[End] Is Null And EmployeeId = " & Me.txtUserID) >0

Note the square brackets around End. Long term, I would suggest changing the name, regardless of whether its an issue right now. E.g. you could call it EndTime instead.

The other potential issue is if your EmployeeID is not numeric. Is the data type of the employeedID field a number? Or is it text?

[Sandy1992]

Yeah tried your suggestion. Still not working. Even I tried placing EndTime instead of Duration. It still shows res

While the code was displaying 2471 error.
"The expression you entered as a query parameter produced error: [End]"

EmployeeId is the numeric field.

[TheSmileyCoder]

It would be easier to follow your debugging if you post the exact code you tried. I am having trouble following your verbal description. Could you maybe do a screenshot of a few records, filtered for a single employee as well? At the moment I don't have enough information to see what the issue could be.

[Sandy1992]

Thank you for your reply smiley.

I have tried some of the logic's. And not even one out of them are serving my requirements.

Probably if post all of the code it may confuse you. So I will give no.s to each of the code.

Lets start :)

1)I used textbox to check if the EmployeeId is present in the query ("TasksQuery ").
So, I used below code under onClick() event.

Me.checktime = DLookup("[EmployeeId]", "TasksQuery", "EmployeeId = " & Me.[txtUserID] & "")
 
 If Me.checktime > 0 Then
 MsgBox ("i>0")
 
 ElseIf Me.checktime = 0 Then
 MsgBox "i=0"
 Else
 MsgBox "Else"
 End If
 Debug.Print Me.checktime

I included elseif and all just to debug and track where my code is going wrong. And always, the value displayed is =0.

2) I used Variable to get the the count if it's greater than zero.
And I always got Null.

i = DCount("*", "TasksQuery", "[EmployeeId]=" & Me.txtUserID)
Debug.Print i
If i > 0 Then
MsgBox "Dcount >0"
ElseIf i = 0 Then
     MsgBox "i=0"
 End If

3) I used below code which is also not working well.

 Dim strSQL As String
 Dim varWhatever As Variant
 Dim rstReadTheManual As DAO.Recordset

 strSQL = "SELECT TasksQuery.EmployeeId FROM TasksQuery where TasksQuery.EmployeeId=" & Me.txtUserID & ";"
 Debug.Print strSQL
 Set rstReadTheManual = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

 With rstReadTheManual
 If (.RecordCount > 0) Then
 varWhatever = ![EmployeeId]

 Else
 Debug.Print varWhatever
 MsgBox "Oops! No records!"
 End If

Kindly advise what is wrong with my code!

Any kind of suggestions are welcomed :)

[TheSmileyCoder]

@1) If you open the TasksQuery, can you manually find the employeeID?
If you add the following (For debugging)

Msgbox Me.txtUserID

Does it display the correct UserID, as you would expect?
Are you using a single view form, split form, datasheet, continous form?

It seems to me now, that the problem is likely either the query, or the value in txtUserID, as it seems to be a similar problem affecting 2). Could you post the SQL of the TasksQuery?

[Sandy1992]

Yes Smiley.
When I debug Me.txtUserID displays the expected result.

I am using single view form.

For TasksQuery I created the query using wizard and mentioned "False" in the criteria of the CheckEndTime field. So, it displays only the data which contains the Checkbox unchecked.

[TheSmileyCoder]

Sorry, but at this point I have nothing else to offer. From what I can see here, it should be working.

If possible you can try zipping up the db and attaching it.

[Sandy1992]

Sorry Smiley. I don't have rights to do that.

Can you suggest me some code from which I can check if the Duration field is empty for the specific user?

[jforbes]

Sandy,

Have you used the VBA Editor, Immediate Window (Ctrl-G to Open) to see what your DCount function is returning?

If you type a test expression like the following into your Immediate Window and hit Enter on it, you should be able to see what it would return if it was executed in your Form Code:

?DCount("Duration", "Tasks", "[End] Is Null And EmployeeId = 1")
?DCount("EmployeeId", "Tasks", "EndTime Is Null")
?DCount("Duration", "TasksQuery", "[Check EndTime] <> 0")
?DCount("Duration", "Tasks", "End Is Null And EmployeeId = " & Me.txtUserID)
?DCount("EmployeeId", "Tasks", "[EndTime] Is Null And EmployeeId = " & Me.txtUserID")

The last couple lines will only work in the Immediate Window if you are in Debug Mode on the Form as Access won't be able to resolve Me.txtUserID until the Form code is executing.

[Sandy1992]

Thank you for your reply J.

I tried debugging in Immediate Window to see what all I am getting. This is what I got.

For Line 1:
I got Run time error '2471'
The Expression you entered as a query parameter produced this error:'[End]'

For Line 2:
I got Run time error '2471'
The Expression you entered as a query parameter produced this error:'EndTime'

For Line 3:
I got Run time error '2471'
The Expression you entered as a query parameter produced this error:'[CheckEndTime]'

For Line 4:
Same as line 1 error

For Line 5:
Same as line 2 error

[zmbd]

Sandy,
Just to confirm, you did have the form loaded and in the debug state (you answered [debug] at the error prompt or have a STOP command in code, which opens the VBA-Editor) when you attempted to use jforbes' code in post12?

[jforbes]

Looks to me that none of the following are actual columns in your tables: End, EndTime, Check Endtime.

The DCount function is equivalent of you creating an Query against a table in the Query Tool, then counting how many records are returned. So in this case, I would recommend trying to get it to work in the Query Tool/Editor first, then taking the code from there and creating the VBA out of it.

• I would Create a New Query by clicking Query Design off the Ribbon.
• Then selecting the Table you want a count of records from. Then add the Primary Key to the Grid.
• Then add the End Time Column (by whatever name it is).
• Then enter IS NULL for criteria for the End Time.
• Then click SQL View on the Ribbon to view the SQL that is generated from this.
• Now that you are viewing the SQL, the First Line selects the Columns and the Primary Key should be here. This usually translates to the First Parameter in the DCount() Function.
• The Second Row has the Table to select from and this translates to the Second Parameter in the DCount() Function.
• The Third Line is the Where Clause and the Stuff between the Parenthesis is what translates to the Third Parameter of the DCount() Function.

This is an example of some Access Generated SQL that is similar to what you are working on:

SELECT Employees.[iCODE]EmployeeID[/iCODE], Employees.CreateDate
FROM [iCODE]Employees[/iCODE]
WHERE (((Employees.[iCODE]CreateDate[/iCODE])[iCODE] Is Null[/iCODE]));

To create a DCount from this the code would be:
DCount("Employe eID", "Employees" , "CreateDate Is Null")