update using for each next statement maybe ?

[Richey]

Hi

I've got a main table called tblRiskMain with an identity column called intRiskIDcalled and a column called NextReview (type smalldatetime)

sample data
-----------
10, 01/01/2004
11, 01/01/2000

i have another table called tblStratReviews with
columns intStratID, intRiskID, ReviewDate

sample data
-----------

1, 10, 01/01/2005
2, 10, 01/01/2006
3, 10, 01/01/2007
4, 11, 01/01/2000
5, 11, 01/01/2003
6, 11, 01/01/2006

when a certain report runs I'd like it to loop through tblStratReviews where ReviewDate >= today's date and then update tblRiskMain with the first recod it finds so for record (intRiskID) 10 the new NewtReview will be 01/01/2006 and for record 11 the new NextReview will be 01/01/2006

thanks for your time

kim

 

[Crowley16]

there's no need for a for loop

you can get the result you want from a single query, and then you can either open the query with a recordset and update from that or build a update query...

what you are looking for in the query is something like:

select top 1 dte
from tbl
where id = 10 and dte > today
order by dte

--------------------
Procrastinate Now!

 

[Crowley16]

oops, the order by bit should be bolded as well...

--------------------
Procrastinate Now!

 

[rubbernilly]

Try this...

Public Sub ThisProcedure()
On error goto Err_ThisProcedure
dim a as integer
dim rsStrat as dao.recordset, rsRisk as dao.recordset
dim strSQL as string

strsql = "SELECT intRiskID, First(ReviewDate) as NextReview FROM tblStratReviews WHERE ReviewDate>=" & Date() & "GROUP BY intRiskID"

set rsstrat = currentdb.openrecordset(strsql)

if rsstrat.eof then
   msgbox "No records in tblStratReviews", vbokonly, "No Records"
   goto Exit_ThisProcedure
end if
rsstrat.movelast
rsstrat.movefirst

for a = 1 to rsstrat.recordcount
   strsql = "SELECT * FROM tblRiskMain WHERE intRiskID = " & rsstrat!intRiskID
   set rsRisk = currentdb.OpenRecordset(strsql)
   if rsrisk.eof then
      rsrisk.addnew
   else
      rsrisk.movefirst
      rsrisk.edit
   end if
         rsrisk!inRiskID = rsstrat!intRiskID
         rsrisk!NextReview = rsstrat!NextReview
      rsrisk.update
   set rsrisk = nothing
   rsstrat.movenext
next a

Exit_ThisProcedure:
set rsStrat = nothing
set rsRisk = nothing
Exit sub

Err_ThisProcedure:
msgbox err.description
resume Exit_ThisProcedure

End Sub

That code is off-the-cuff, so you might have to watch it once or twice to make sure it does what you want.

Couple of caveats...

If your data type on the intRiskID is an integer or long (autonumber), then the code I provided should work. However, if it is a string, then the line:

[tt]strsql = "SELECT * FROM tblRiskMain WHERE intRiskID = " & rsstrat!intRiskID[/tt]

should be rewritten:

[tt]strsql = "SELECT * FROM tblRiskMain WHERE intRiskID = " & chr$(34) & rsstrat!intRiskID & chr$(34)[/tt]

Also, I have seen the First() function return screwy data in an aggregate query like I used for the first strSQL statement. If it does not return the correct Review Date, then you might need one more recordset:

Create a dao recordset grouping on the intRiskID in your tblStratReview table
Loop 'a': For each record, construct a dao recordset selecting everything from the tblStratReview table where the intRiskID equals the intRiskID from the first recordset
Move first
Create a dao recordset selecting everything from the tblRiskMain where the intRiskID matches the Loop-created recordset.
Add or update as needed, as per the code.

This will make sure that you get the information you are looking for.

HTH

 

[PHV]

In the SQL pane of the query window:
UPDATE tblRiskMain
SET NextReview=DMin("ReviewDate", "tblStratReviews", "ReviewDate>=Date() AND intRiskID=" & [intRiskID])
WHERE NextReview < Date()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Richey]

thanks everyone
I eventually have got it working using my code below

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim stra, strupdatea As String
Dim rst As DAO.Recordset

stra = "SELECT intStratID, Min(ReviewDate) AS NextDate FROM dbo_tblStratReviews where ReviewDate >= date() GROUP BY intStratID"
Set rst = dbs.OpenRecordset(stra, dbOpenDynaset, dbseechanges)
If Not rst.EOF Then
Do Until rst.EOF
strupdatea = "update dbo_tblRiskMain set nextreview = '" & rst("NextDate") & "' where intRiskID = " & rst("intStratID")
dbs.Execute (strupdatea), dbseechanges
rst.MoveNext
Loop
Else
End If