Compile Error: Object required (1 Viewer)

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!!!!

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;"

Me![lstMonth].Value = Null is NOT a valid way to check for nulls. This statement will ALWAYS be false regardless of the value of lstMonth.
IsNull(Me.[lstMonth]) Is correct. Read help or search here for further explaination on null.

Month and Year are both function names and as such should NEVER be used to name user objects and will occassionally cause errors so if the missing spaces don't fix the problem, I would change the column names. I would strongly suggest changing the column names in any event.

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!

Did you change your column names. You should not be using [month] and [year] since they are function names. You can get away with this if you never use any VBA but once you try to use these names in code, you run into problems. To see if that is really the problem, you can test the effect of changing the names by creating a query that renames the poorly named columns and then you can reference the saved querydef in the SQL string rather than the table.

query1:

Select tbl1.Month As MyMonth, tbl1.Year As MyYear, .....

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

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