Q? DoCmd RunSQL Update query

[niuginikiwi]

I am trying to update a date field on table tblApplications using the
code on command button btnGo as shown below. I think the procedure
runs the query as expected but then JET engine says " you are about
to update 0 rows, Yes to accept or No to cancel"....
But I know that I have a record present that I am trying to update.
Can anyone help me on this please?

Private Sub btnGo_Click()
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim SQL As String
'Stop
'update sql string
SQL = "UPDATE tblApplications " & _
"SET tblApplications.ApplicationDate = " & Format(Me!
txtNewApplicationDate, conDateFormat) & _
"WHERE tblApplications.ApplicationDate = " & Format(Me!
txtApplicationDate, conDateFormat) & " AND tblApplications.OperationID
= " & Me!cboOperation.Column(0) & " AND
tblApplications.PlantingDetailsID = PlantingDetailsID" &
MultiSelectSQL(Me!lstPlantings)
'run the update query
DoCmd.RunSQL SQL
End Sub

 

[SteveS]

You have this as the last condition of the WHERE clause:

" AND tblApplications.PlantingDetailsID = PlantingDetailsID" &
MultiSelectSQL(Me!lstPlantings)


You have an extra "PlantingDetailsID" after the equal sign. Shouldn't it be:


" AND tblApplications.PlantingDetailsID = " & MultiSelectSQL(Me!lstPlantings)


HTH

 

[niuginikiwi]

Steve,
The other PlantingDetailsID just before the calling multiselectSQL
function is meant to be there.
It comes from a multiselect listbox with the multiselectSQL() iterates
through the list and gets itemselected and returns them in the form of
In(123, 231, 254) etc...

I just cant understand why there is a record present and all the where
conditions are met but it can not be updated.

 

[SteveS]

Not having your database to try or any examples, the only things I could
suggest would be changing the SQL update query to a Select query (recordset)
to see how many records (if any) are returned. Add a message box to report
the record count of the recordset.

And/Or you could add "Debug.Print SQL" and comment out the "DoCmd" to see if
the SQL generated is a valid statement.