-
Using Combo Boxes to input into SQL for updating a table
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|