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
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) & _
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
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" <an...@anon.net> wrote in message
news:eduo0R85...@TK2MSFTNGP03.phx.gbl...
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!
--
--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" <NO-Roger.J....@gmail.com> wrote in message
news:%23mWTVa8...@TK2MSFTNGP04.phx.gbl...
Note:
rst!RollNo
and
rst("RollNo")
are equivalent.
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.
--
Marsh
MVP [MS Access]