RunSQL statement in DAO loop

[Al Campagna]

**Posted this about 2 hours ago, and it still hasn't shown up. Pardon if a duplicate...

I'm parsing down through many records using DAO loop , examining values as I go.
If a certain condition is met, I need to Update one or more records that were previously
accessed via the loop, and set those values to 0.
Thought I could insert an Update query to accomplish that, and then continue on to the
next DAO record.

Dim rst As DAO.Recordset
Set rst = Form.RecordsetClone
PreviousLocationScore = DSum("[RawScore]", "qryDefectsGraphedScoring",
"GraphedID < " & rst("GraphedID") & " And DefectLocation = " & rst("DefectLocation"))
' *** PreviousLocationScore returns correct result
If rst("DefectLength") >= 1000 Then
rst.Edit
rst("FinalScore") = rst("RawScore")
rst("ScoreCode") = " D/LS " & rst("DefectLocation") & " = " & LastLocation
rst.Update
' *** Update those previous records (RollNo text, DefectLocation integer, GraphedID long)
DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " & _
"WHERE (((tblDefectsGraphed.RollNo) = rst('RollNo') AND
((tblDefectsGraphed.DefectLocation) = rst('DefectLocation') AND
((tblDefectsGraphed.GraphedID) < rst('[GraphedID]')));"
GoTo DoneScoring
End If

The RunSQL code does not work. No Errors, no Update warning mesage.
Perhaps the DAO needs to be closed and re-opened?
Perhaps the query can not "see" the rst values?
Could I accomplish the Update using DAO?

Any help appreciated... couldn't figure out what to search the Access groups with
to find an answer.

Thanks,
Al Campagna

 

[Dirk Goldgar]

**Posted this about 2 hours ago, and it still hasn't shown up.
Pardon if a duplicate...

That's odd, Al. Three of us have replied to your original message.

 

[Douglas J. Steele]

Not only did it appear earlier, Al, but I saw 2 or 3 responses to it
(although I suppose it could have been a different newsgroup).

The issue is that you're referring to fields from your recordset inside the
SQL. You have to pass their values, not references to them.

Assuming all fields are numeric, try:

DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore =
0 " & _
"WHERE tblDefectsGraphed.RollNo = " & rst("RollNo") & " AND " & _
"tblDefectsGraphed.DefectLocation = " & rst("DefectLocation") & " AND "
& _
"tblDefectsGraphed.GraphedID < " & rst("[GraphedID]"

For text fields, you'll need to put quotes around the values, like:

"tblDefectsGraphed.DefectLocation = '" & rst("DefectLocation") & "' AND
" & _

where,exagerated for clarity, that's

"tblDefectsGraphed.DefectLocation = ' " & rst("DefectLocation") & " '
AND " & _

 

[Al Campagna]

Doug and Dirk,
This is really strange... I still don't see my previous post, some 9 hours later.
I still have the original post, and it was sent to
(microsoft.public.access.formscoding)
Can't understand why I can't see it.
I checked every Access group I subscribe to, and can't find it at all. Only this
post...
Have no idea what's going on...?? Seems to me that I had this problem (on just one
post) about a year ago.
Sorry that I can't respond to the other posts...

Doug,
I'll certainly give your suggestion a go, and I will get backon this thread... yea
or nay... ASAP.

Thanks for your patience everyone,
Al Campagna

Not only did it appear earlier, Al, but I saw 2 or 3 responses to it (although I suppose
it could have been a different newsgroup).

The issue is that you're referring to fields from your recordset inside the SQL. You
have to pass their values, not references to them.

Assuming all fields are numeric, try:

DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " & _
"WHERE tblDefectsGraphed.RollNo = " & rst("RollNo") & " AND " & _
"tblDefectsGraphed.DefectLocation = " & rst("DefectLocation") & " AND " & _
"tblDefectsGraphed.GraphedID < " & rst("[GraphedID]"

For text fields, you'll need to put quotes around the values, like:

"tblDefectsGraphed.DefectLocation = '" & rst("DefectLocation") & "' AND " & _

where,exagerated for clarity, that's

"tblDefectsGraphed.DefectLocation = ' " & rst("DefectLocation") & " ' AND " & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

**Posted this about 2 hours ago, and it still hasn't shown up. Pardon if a
duplicate...

I'm parsing down through many records using DAO loop , examining values as I go.
If a certain condition is met, I need to Update one or more records that were
previously
accessed via the loop, and set those values to 0.
Thought I could insert an Update query to accomplish that, and then continue on to
the
next DAO record.

Dim rst As DAO.Recordset
Set rst = Form.RecordsetClone
PreviousLocationScore = DSum("[RawScore]", "qryDefectsGraphedScoring",
"GraphedID < " & rst("GraphedID") & " And DefectLocation = " & rst("DefectLocation"))
' *** PreviousLocationScore returns correct result
If rst("DefectLength") >= 1000 Then
rst.Edit
rst("FinalScore") = rst("RawScore")
rst("ScoreCode") = " D/LS " & rst("DefectLocation") & " = " & LastLocation
rst.Update
' *** Update those previous records (RollNo text, DefectLocation integer, GraphedID
long)
DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = rst('RollNo') AND
((tblDefectsGraphed.DefectLocation) = rst('DefectLocation') AND
((tblDefectsGraphed.GraphedID) < rst('[GraphedID]')));"
GoTo DoneScoring
End If

The RunSQL code does not work. No Errors, no Update warning mesage.
Perhaps the DAO needs to be closed and re-opened?
Perhaps the query can not "see" the rst values?
Could I accomplish the Update using DAO?

Any help appreciated... couldn't figure out what to search the Access groups with
to find an answer.

Thanks,
Al Campagna

 

[Dirk Goldgar]

Doug and Dirk,
This is really strange... I still don't see my previous post, some
9 hours later. I still have the original post, and it was sent to
(microsoft.public.access.formscoding)
Can't understand why I can't see it.

I don't either, Al. Here's a link to the thread on Google Groups:


http://groups.google.com/group/micr...read/thread/997953680b53f76a/b8a6db99364c278f

And here's a quote of my original response:

I'm parsing down through many records using DAO loop , examining
values as I go.
If a certain condition is met, I need to Update one or more records
that were previously accessed via the loop, and set those values to
0. Thought I could insert an Update query to accomplish that, and
then continue on to the next DAO record.

PreviousLocationScore = DSum("[RawScore]",
"qryDefectsGraphedScoring", "GraphedID < " & rst("GraphedID") & "
And DefectLocation = " & rst("DefectLocation")) ' ***
PreviousLocationScore returns correct result If
rst("DefectLength") >= 1000 Then rst.Edit
rst("FinalScore") = rst("RawScore")
rst("ScoreCode") = " D/LS " & rst("DefectLocation") & " = " &
LastLocation rst.Update
' *** Update those previous records (RollNo text, Location Integer,
GraphedID long) DoCmd.RunSQL "UPDATE tblDefectsGraphed SET
tblDefectsGraphed.FinalScore = 0 " & _ "WHERE
(((tblDefectsGraphed.RollNo) = rst('RollNo') AND
((tblDefectsGraphed.DefectLocation) = rst('DefectLocation')
AND ((tblDefectsGraphed.GraphedID) < rst('[GraphedID]')));" GoTo
DoneScoring End If

The RunSQL code does not work. No Errors, no Update warning
mesage. Perhaps the DAO needs to be closed and re-opened?
Perhaps the query can not "see" the rst values?
Could I accomplish the Update using DAO?

I wouldn't expect your use of recordset references within the query
string to work. Does it work if you embed the values from the
recordset instead of the references? Also, I'm not sure all your
parentheses match up. Does the following work? I've taken the
recordset references out of the string and removed superfluous
parentheses.

"WHERE RollNo = " & rst("RollNo") & _
" AND DefectLocation = " & rst("DefectLocation") & _
" AND GraphedID < " & rst("[GraphedID]")

Note: the above assumes that all of those fields are numeric. If any
of them is text, you'll need to surround the embedded value with
quotes. For example, if DefectLocation is text, you might write:

" AND DefectLocation = " & _
Chr(34) & rst("DefectLocation") & Chr(34) & _

 

[Al Campagna]

Douglas,
This is what I ended up with...
DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " & _
"WHERE tblDefectsGraphed.RollNo = '" & rst("RollNo") & "' AND " & _
"tblDefectsGraphed.DefectLocation = " & rst("DefectLocation") & " AND " & _
"tblDefectsGraphed.GraphedID < " & rst("[GraphedID]")

Works like a champ!

(RollNo was the text culprit) Missed that completely... and got hung up on a possible DAO
issue that wasn't really there.
The old "can't see the forest for the trees" syndrome.

Thank you very much... and thanks everyone that tried to help on my "missing" post..
Al Campagna

Not only did it appear earlier, Al, but I saw 2 or 3 responses to it (although I suppose
it could have been a different newsgroup).

The issue is that you're referring to fields from your recordset inside the SQL. You
have to pass their values, not references to them.

Assuming all fields are numeric, try:

DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " & _
"WHERE tblDefectsGraphed.RollNo = " & rst("RollNo") & " AND " & _
"tblDefectsGraphed.DefectLocation = " & rst("DefectLocation") & " AND " & _
"tblDefectsGraphed.GraphedID < " & rst("[GraphedID]"

For text fields, you'll need to put quotes around the values, like:

"tblDefectsGraphed.DefectLocation = '" & rst("DefectLocation") & "' AND " & _

where,exagerated for clarity, that's

"tblDefectsGraphed.DefectLocation = ' " & rst("DefectLocation") & " ' AND " & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

**Posted this about 2 hours ago, and it still hasn't shown up. Pardon if a
duplicate...

I'm parsing down through many records using DAO loop , examining values as I go.
If a certain condition is met, I need to Update one or more records that were
previously
accessed via the loop, and set those values to 0.
Thought I could insert an Update query to accomplish that, and then continue on to
the
next DAO record.

Dim rst As DAO.Recordset
Set rst = Form.RecordsetClone
PreviousLocationScore = DSum("[RawScore]", "qryDefectsGraphedScoring",
"GraphedID < " & rst("GraphedID") & " And DefectLocation = " & rst("DefectLocation"))
' *** PreviousLocationScore returns correct result
If rst("DefectLength") >= 1000 Then
rst.Edit
rst("FinalScore") = rst("RawScore")
rst("ScoreCode") = " D/LS " & rst("DefectLocation") & " = " & LastLocation
rst.Update
' *** Update those previous records (RollNo text, DefectLocation integer, GraphedID
long)
DoCmd.RunSQL "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = rst('RollNo') AND
((tblDefectsGraphed.DefectLocation) = rst('DefectLocation') AND
((tblDefectsGraphed.GraphedID) < rst('[GraphedID]')));"
GoTo DoneScoring
End If

The RunSQL code does not work. No Errors, no Update warning mesage.
Perhaps the DAO needs to be closed and re-opened?
Perhaps the query can not "see" the rst values?
Could I accomplish the Update using DAO?

Any help appreciated... couldn't figure out what to search the Access groups with
to find an answer.

Thanks,
Al Campagna