Using VBA to Update an checkbox

[wazzu1997]

Hello,

I am new to this posting stuff, but here is my current dilema . . . .

I have a checkbox in one of my tables and I need for it to update using an update query. Now using straight Access I can just say UPDATE tblname.checkboxfiled=Yes or even -1 and I get the good result.

The problem is I am using ome VBA coding and it doesn't seem to like the =Yes or =-1. I tried putting them in single quotes and I get a data conversion error.

My code is as follows:

Private Sub cmdRoleExists_Click()
 
Dim DB As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
 
Set DB = CurrentDb()
Set qdf = DB.QueryDefs("qrySAPSecurityRoleExistsUpdate")
 
For Each varItem In Me!lstSecuritySAP.ItemsSelected
    strCriteria = strCriteria & "," & Me!lstSecuritySAP.ItemData(varItem)
Next varItem
 
If Len(strCriteria) = 0 Then
    MsgBox "You must select at least 1 SAP #" _
            , vbExclamation, "No Selection Made"
    Exit Sub
End If
 
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
 
strSQL = "UPDATE tblTrackingData SET tblTrackingData.[SAP Security Role Status] = 'Completed - ' & Date(), tblTrackingData.[SAP Security Role] = Yes" & _
"WHERE tblTrackingData.[SAP #] IN(" & strCriteria & ")"
 
qdf.SQL = strSQL
 
DoCmd.OpenQuery "qrySAPSecurityRoleExistsUpdate"
 
Set DB = Nothing
Set qdf = Nothing
 
End Sub
 

When I remove the tblTrackingDAta.[SAP Security Role] = Yes part of the code (which is the checkbox field) the rest works just fine.

Any thoughts would be most appreciated.

Thanks!

[Scott Price]

Try using True instead of Yes.

Regards,
Scott

[wazzu1997]

Thank you for your response.

When I change the last part of the code to tblTrackingData.[SAP Security Role] = True" & _ I get the same error message as when I have Yes or -1 in there.

the message is this . . . . .

Run-time error '3705':

Syntax error (missing operator) in query expression 'TrueWHERE tblTrackingData.[SAP #] IN(7397)'.

When I put single quotes around the True or Yes ot -1, This message does not appear but I get teh conversion type error.

However, on a whim I tried it with Parentheses . . . tblTrackingData.[SAP Security Role] = (Yes)" & _ and to my surprise it worked like a charm.

Thanks for your efforts and this can be closed!!!

[Scott Price]

That is indeed strange... I've never seen the parentheses needed in just that way, but if it's working for you, I'm glad to hear it!

Thanks for posting back with the solution.

Regards,
Scott

[NeoPa]

Please look at lines #24 & #25 in the code you posted. You will notice if you look, that there is no resultant space before the "WHERE" clause starts (line #25). Therefore, you are actually trying to set the field to a value of YesWHERE!
This won't generally work.
This is a very common problem when building strings of SQL in VBA.

[NeoPa]

By the way, it's always a good idea when posting code to quote the line number(s) where the error occurrs. It's so much easier to work with, therefore more likely to find an answer.