UPDATE query not recognizing variable as field name

[GraeagleBill]

I get a 3061 runtime error with the following code. I'm assuming the UPDATE query is not recognizing "ChkBox" as a variable string containing the field name. If that is in fact the case, what's the syntax required so that the "ChkBox" string is understood as a field name?

Private Sub SetTable(ChkBox As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  From the name of the check box control, we can determine which column and
'  hense which table and field needs to be initialized.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
On Error GoTo Err_Handler


Select Case left(ChkBox, 4)
    Case "ck11": CurrentDb.Execute "UPDATE tblCol1 SET chkbox = True"          'User number 1, period 1
    Case "ck12": CurrentDb.Execute "UPDATE tblCol2 SET chkbox = True"          'User number 1, period 2
    Case "ck21": CurrentDb.Execute "UPDATE tblCol3 SET chkbox = True"          'User number 2, period 1
    Case "ck22": CurrentDb.Execute "UPDATE tblCol4 SET chkbox = True"          'User number 2, period 2
End Select


Exit_Handler:
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " attempting to initialize column table : " & Err.Description
   Resume Exit_Handler
   
End Sub

[Welshgasman]

So you would concatenate the variable into the sql string?

[Minty]

You haven't concatenated the variable into your SQL string.

 Case "ck11": CurrentDb.Execute "UPDATE tblCol1 SET " & chkbox & " = True"

[GraeagleBill]

GOOD GRIEF! Of course! Age is taking its toll. (Have no fear, one day you'll get yours )
Thanks,
Bill

EDIT: YIKES, I thought I was done with this! None of the column (tblColx) tables got updated, though no errors were issued?

Private Sub SetTable(ChkBox As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  From the name of the check box control, we can determine which column and
'  hense which table and field needs to be initialized.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
On Error GoTo Err_Handler


Select Case left(ChkBox, 4)
    Case "ck11": CurrentDb.Execute "UPDATE tblCol1 SET " & ChkBox & " = True"          'User number 1, period 1
    Case "ck12": CurrentDb.Execute "UPDATE tblCol2 SET " & ChkBox & " = True"          'User number 1, period 2
    Case "ck21": CurrentDb.Execute "UPDATE tblCol3 SET " & ChkBox & " = True"          'User number 2, period 1
    Case "ck22": CurrentDb.Execute "UPDATE tblCol4 SET " & ChkBox & " = True"          'User number 2, period 2
End Select


If Me.Dirty Then Me.Dirty = False


Exit_Handler:
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " attempting to initialize column table : " & Err.Description
   Resume Exit_Handler
   
End Sub

I ran the code in Debug and all looked as expected, but still don't see the table fields being updated.


EDIT: Just so you'll know the table properties:

[Minty]

Comment out the error handler in case it's hiding something?
What happens if you manually run the update statement.

I'd also manoeuvre your code around so you know it's running;

Private Sub SetTable(ChkBox As String)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  From the name of the check box control, we can determine which column and
'  hense which table and field needs to be initialized.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'On Error GoTo Err_Handler


Dim strSQL as String


Select Case left(ChkBox, 4)
    Case "ck11": strSQL =  "UPDATE tblCol1 SET " & ChkBox & " = True"          'User number 1, period 1
    Case "ck12": strSQL =  "UPDATE tblCol2 SET " & ChkBox & " = True"          'User number 1, period 2
    Case "ck21": strSQL =  "UPDATE tblCol3 SET " & ChkBox & " = True"          'User number 2, period 1
    Case "ck22": strSQL =  "UPDATE tblCol4 SET " & ChkBox & " = True"          'User number 2, period 2
End Select
debug.print strSQL
Currentdb.Execute strSQL


If Me.Dirty Then Me.Dirty = False




Exit_Handler:
   Exit Sub
Err_Handler:
   MsgBox "Error " & Err.Number & " attempting to initialize column table : " & Err.Description
   Resume Exit_Handler
   
End Sub