Compile Error: Object required

[swes912]

compile error: object required- - - -Help!

On a form I am trying to update a table with Month and Year that user chooses. I did a test and know that the date from the form is coming through good. The error I get is a compile error object required on the Set stSQL = "......."


Private Sub cmdStartForm_Click()

If (Me![lstMonth].Value = Null) Then
MsgBox "Please highlight a Month.", vbOKOnly
End If
If (Me![lstYear].Value = Null) Then
MsgBox "Please highlight a Year.", vbOKOnly
End If

Dim con As Object
Dim rs As Object
Dim stSQL As String
Dim intOption As Integer

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("adodb.recordset")
Set stSQL = "UPDATE [Basin_Supplemental_Demand]SET [month] = " & Me![lstMonth].Value & ", [year] = " & Me![lstYear].Value & "WHERE bsdID=1;"
rs.Open stSQL, con, 1 '1 = adOpenKeyset

Thanks for the help!!!!

 

[dcx693]

You just have an issue with spaces in your statement before the SET keyword and before the WHERE keyword. Try this:
Set stSQL = "UPDATE [Basin_Supplemental_Demand] SET [month] = " & Me![lstMonth].Value & ", [year] = " & Me![lstYear].Value & " WHERE bsdID=1;"

The .Value property is the default property for controls. You can shorten the command to this:
Set stSQL = "UPDATE [Basin_Supplemental_Demand] SET [month] = " & Me![lstMonth] & ", [year] = " & Me![lstYear] & " WHERE bsdID=1;"

 

[swes912]

I tried both of your recomendations.
I seperated the code to diff lines using stSQL = stSQL & .....
and I get the same compile error on every variable even on
Me![lstYear].
Is there some setting that I am missing.
Kinda at a loss!

 

[swes912]

yes i did change the names. This is what the new code looks like
If IsNull(Me.[lstMonth]) Then
MsgBox "Please highlight a Month.", vbOKOnly
End If
If IsNull(Me.[lstYear]) Then
MsgBox "Please highlight a Year.", vbOKOnly
End If

Dim con As Object
Dim rs As Object
Dim stSQL As String
Dim intOption As Integer

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("adodb.recordset")
Set stSQL = "UPDATE [Basin_Supplemental_Demand] SET [bsdMonth] =" & Me![lstMonth] & ", [bsdYear] =" & Me![lstYear].Value & " WHERE bsdID=1;"
rs.Open strtest, con, 1 '1 = adOpenKeyset

On Error GoTo Err_cmdStart_Click
Exit_cmdStart_Click:
Exit Sub

Err_cmdStart_Click:
MsgBox Err.Description
Resume Exit_cmdStart_Click

End Sub

I appreciate all of the help received

 

[swes912]

I took Set out and just did stSQL = ..... And it worked.
Thanks for the help