RunSQL Update within DAO

A

Al Campagna

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?

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

Thanks,
Al Campagna
 
R

Roger Carlson

Your SQL string is wrong. I always assign it to a string variable first,
though. It makes debugging easier:
dim strSQL as String
strSQL = "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = " & rst('RollNo') & " AND "
& _
"((tblDefectsGraphed.DefectLocation) = " & rst('DefectLocation') &
" AND " & _
"((tblDefectsGraphed.GraphedID) < " & rst('[GraphedID]') & "));"
debug.print strSQL
db.Execute strSQL, dbFailOnError

Notes:

1) The above assumes all fields are numeric. If DefectLocation is Text,
however, the string would look like this:
strSQL = "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = " & rst('RollNo') & " AND "
& _
"((tblDefectsGraphed.DefectLocation) = '" & rst('DefectLocation') &
"' AND " & _
"((tblDefectsGraphed.GraphedID) < " & rst('[GraphedID]') & "));"
Notice the additional apostrophes one either side of the quotes around
DefectLocation?

2) the debug.print line with print the evaluated SQL statement to the
immediate window. Put a Breakpoint on the line following and execution will
stop and you will see exactly what the SQL engine will see.

3) db.Execute is faster than RunSQL and won't ask for confirmation.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Al Campagna said:
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
Click to expand...
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
Click to expand...
 
D

Dirk Goldgar

Al Campagna said:
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?
Click to expand...

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) & _
 
J

Jeff L

First of all the syntax is rst!FieldName and second of all you cannot
include variables in an SQL string the way you have them. It should be
like this:

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

Hope that helps!
 
R

Roger Carlson

I should mention that after you get the thing working, you can delete or
comment out the Debug.Print line.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger Carlson said:
Your SQL string is wrong. I always assign it to a string variable first,
though. It makes debugging easier:
dim strSQL as String
strSQL = "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = " & rst('RollNo') & " AND "
& _
"((tblDefectsGraphed.DefectLocation) = " & rst('DefectLocation') &
" AND " & _
"((tblDefectsGraphed.GraphedID) < " & rst('[GraphedID]') & "));"
debug.print strSQL
db.Execute strSQL, dbFailOnError

Notes:

1) The above assumes all fields are numeric. If DefectLocation is Text,
however, the string would look like this:
strSQL = "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = " & rst('RollNo') & " AND "
& _
"((tblDefectsGraphed.DefectLocation) = '" & rst('DefectLocation') &
"' AND " & _
"((tblDefectsGraphed.GraphedID) < " & rst('[GraphedID]') & "));"
Notice the additional apostrophes one either side of the quotes around
DefectLocation?

2) the debug.print line with print the evaluated SQL statement to the
immediate window. Put a Breakpoint on the line following and execution will
stop and you will see exactly what the SQL engine will see.

3) db.Execute is faster than RunSQL and won't ask for confirmation.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Al Campagna said:
I'm parsing down through many records using DAO loop , examining
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
M

Marshall Barton

Al said:
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?
Click to expand...


Al,

After you sort out the syntax issues and get the table
updated, be aware the RunSQL runs the the query
asynchronously. This means that the data may not get there
immediately. Try using the DAO Execute method instead.

I don't see in your code where it might matter, but, just
in case you were expecting more, I don't think your
recordset will reflect the results of the UPDATE query. You
can Requery the recordset, but that may mean that you would
need to restart the loop.
 
A

Al Campagna

Roger,
For some reason I couldn't see my post when using OExpress.
Something went haywire. Everybody else could see it, and responed, but
I couldn't... I'm responding through Google Groups, and I can see my
original post and all the responses.
Very odd...
I repaired the RollNo (text) part of the SQL statement, and
everything works fine.
Sorry for the mixup, and not responding sooner.
And thanks for the help,
Al Campagna


Roger said:
I should mention that after you get the thing working, you can delete or
comment out the Debug.Print line.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger Carlson said:
Your SQL string is wrong. I always assign it to a string variable first,
though. It makes debugging easier:
dim strSQL as String
strSQL = "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = " & rst('RollNo') & " AND "
& _
"((tblDefectsGraphed.DefectLocation) = " & rst('DefectLocation') &
" AND " & _
"((tblDefectsGraphed.GraphedID) < " & rst('[GraphedID]') & "));"
debug.print strSQL
db.Execute strSQL, dbFailOnError

Notes:

1) The above assumes all fields are numeric. If DefectLocation is Text,
however, the string would look like this:
strSQL = "UPDATE tblDefectsGraphed SET tblDefectsGraphed.FinalScore = 0 " &
_
"WHERE (((tblDefectsGraphed.RollNo) = " & rst('RollNo') & " AND "
& _
"((tblDefectsGraphed.DefectLocation) = '" & rst('DefectLocation') &
"' AND " & _
"((tblDefectsGraphed.GraphedID) < " & rst('[GraphedID]') & "));"
Notice the additional apostrophes one either side of the quotes around
DefectLocation?

2) the debug.print line with print the evaluated SQL statement to the
immediate window. Put a Breakpoint on the line following and execution will
stop and you will see exactly what the SQL engine will see.

3) db.Execute is faster than RunSQL and won't ask for confirmation.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Al Campagna said:
I'm parsing down through many records using DAO loop , examining
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
A

Al Campagna

Marshall,
What a fiasco on this post...
For some reason I couldn't see my post when using OExpress.
Something went haywire. Everybody else could see it, and responed, but
I couldn't... I'm responding through Google Groups, and I can see my
original post and all the responses.
Very odd...
I repaired the RollNo (text) part of the SQL statement, and
everything works fine.
Sorry for the mixup, and not responding sooner.
And thanks for the help,
Al Campagna


Marshall said:
Al said:
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?
Click to expand...


Al,

After you sort out the syntax issues and get the table
updated, be aware the RunSQL runs the the query
asynchronously. This means that the data may not get there
immediately. Try using the DAO Execute method instead.

I don't see in your code where it might matter, but, just
in case you were expecting more, I don't think your
recordset will reflect the results of the UPDATE query. You
can Requery the recordset, but that may mean that you would
need to restart the loop.
Click to expand...
 
A

Al Campagna

Jeff,
Please see my reply to Marshall and Roger.
Quite the mix-up on this particular post.
Thanks for your help,
Al Campagna

Jeff said:
First of all the syntax is rst!FieldName and second of all you cannot
include variables in an SQL string the way you have them. It should be
like this:

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

Hope that helps!


Al said:
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?

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

Thanks,
Al Campagna
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RunSQL statement in DAO loop 5
DAO edit and update problem 0

Top