L
LAF
I am having problems using FindFirst and FindLast in the following
subprocedure.
Sub CaptureUpdate( )
Dim db as DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID as String
Dim sSQL as String
Dim Date1 as Date
Dim Date2 as Date
Set db = CurrentDB( )
Set rst1 = db.OpenRecordset("tblBirds",dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandDateSort",dbReadOnly)
' The objective of the procedure is to determine the initial capture date
and the most recent capture date for each individual and to enter the dates
in appropriate fields in tblBirds. The ID is Bandnum, a text field.
tblBirds is sorted by Bandnum. qryBandDateSort is sorted by Bandnum and then
by EvDate.
Do Until rst1.EOF
CurrentID = rst1!BANDNUM
sSQL = "[BANDNUM] = " & CurrentID
rst2.FindFirst sSQL
Date1 = rs2!EvDate
rst2.FindLast sSQL
Date2 = rst2!EvDate
rst1.Edit
rst1!InitialDate = Date1
rst1!RecentDate = Date2
rst1.Update
rst2.MoveFirst
rst1.MoveNext
Loop
Ileft out the close and nothing statementsl
The problem is that this code fills in all InitialDate fields with the
InitialDate for the first bandnum, and fills in all the RecentDate fields
with the RecentDate for the last bandnum.
One potential problem is that some individuals are only captured once, so
InitialDate and RecentDate should be the same. Might this be what is causing
the problem?
Thanks
subprocedure.
Sub CaptureUpdate( )
Dim db as DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID as String
Dim sSQL as String
Dim Date1 as Date
Dim Date2 as Date
Set db = CurrentDB( )
Set rst1 = db.OpenRecordset("tblBirds",dbOpenDynaset)
Set rst2 = db.OpenRecordset("qryBandDateSort",dbReadOnly)
' The objective of the procedure is to determine the initial capture date
and the most recent capture date for each individual and to enter the dates
in appropriate fields in tblBirds. The ID is Bandnum, a text field.
tblBirds is sorted by Bandnum. qryBandDateSort is sorted by Bandnum and then
by EvDate.
Do Until rst1.EOF
CurrentID = rst1!BANDNUM
sSQL = "[BANDNUM] = " & CurrentID
rst2.FindFirst sSQL
Date1 = rs2!EvDate
rst2.FindLast sSQL
Date2 = rst2!EvDate
rst1.Edit
rst1!InitialDate = Date1
rst1!RecentDate = Date2
rst1.Update
rst2.MoveFirst
rst1.MoveNext
Loop
Ileft out the close and nothing statementsl
The problem is that this code fills in all InitialDate fields with the
InitialDate for the first bandnum, and fills in all the RecentDate fields
with the RecentDate for the last bandnum.
One potential problem is that some individuals are only captured once, so
InitialDate and RecentDate should be the same. Might this be what is causing
the problem?
Thanks