Using Combo Boxes to input into SQL for updating a table

[W Sasser]

HELP

I am trying to use variables from combo boxes to update a table. I want the users to be able to select specific data to update a table (namely a single field in the table, and it is boolean)

The code I've written is as such:

Private Sub cmdCalculate_Click()

Dim strShift As String
Dim strYear As String
Dim strMonth As String
Dim strGoal As String
'Dim rsEmpTotal As Recordset
Dim cmdUpdate As ADODB.Command
Dim strSQL As String

Set cmdUpdate = New ADODB.Command
'Set rsEmpTotal = New ADODB.Recordset

strShift = Me.cboShift

intMonth = Me.cboMonth
intYear = Me.cboYear
strGoal = Me.cboGoal

'rsEmpTotal.Open

strSQL = "Update tblEmpTotals SET Achieve = True Where Goal_N = 'strGoal' and Month = 'strMonth' and Year = 'strYear' and Shift = 'strShift'"
'strSQL = "Update tblEmpTotals SET Achieve = True Where Goal_N = 'PPIM'"

Debug.Print strSQL

With cmdUpdate
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

End Sub


The areas I've added the apostrophe, is tried and true. When I debug, the code is NOT transferring from the variables to the strSQL string. I did use the strSQL string in the CommandText first. That didn't work so I moved it outside the variable and it still didn't take it.

In the strSQL I've done it with quotes, without quotes.

HELP

[JBane]

WSasser,

strSQL = "Update tblEmpTotals SET Achieve = True Where Goal_N = 'strGoal' and Month = 'strMonth' and Year = 'strYear' and Shift = 'strShift'"

should be:


strSQL = "Update tblEmpTotals SET Achieve = True Where Goal_N = '" & strGoal & "' and Month = '" & strMonth & "' and Year = '" & strYear & "' and Shift = '" & strShift & "'"

Jeff