Q? DoCmd RunSQL Update query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

niuginikiwi - 10 May 2007 05:55 GMT

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

Reply to this Message

SteveS - 10 May 2007 07:57 GMT

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

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> 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
[quoted text clipped - 19 lines]
> DoCmd.RunSQL SQL
> End Sub

Reply to this Message

niuginikiwi - 10 May 2007 21:35 GMT

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.

Reply to this Message

SteveS - 16 May 2007 10:31 GMT

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.

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> Steve,
> The other PlantingDetailsID just before the calling multiselectSQL
[quoted text clipped - 5 lines]
> I just cant understand why there is a record present and all the where
> conditions are met but it can not be updated.

Reply to this Message