FindFirst and FindLast

G

Guest

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
 
S

SteveS

LAF said:
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

One thing I see is that you should have a rst1.movefirst before the "Do Until...":

' added the .movefirst line
rst1.MoveFirst
Do Until rst1.EOF
 
T

Tim Ferguson

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.

Why are you loading the whole qry if you only want two data items? You
can reduce the network usage and speed up the whole lot and get the right
answer by using the correct recorset:

strSQL = "SELECT EvDate FROM qryBandDateSort " & _
"WHERE BandNum = """ & CurrentID & """ " & _
"ORDER BY EvDate"

Set rst2 = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
If Not rst2.EOF Then]
' counter is already at first record
FirstDate = rst2("EvDate")
' might or might not be the same as the last one in the set
rst2.MoveLast
LastDate = rst2("EvDate")

' etc

Of course this is wasteful too, since you are not reading all the
inbetween records, should there be any. You might consider rewriting the
qryBandDateSort to return only the two wanted fields (say, by passing the
BandNum as a parameter) or, more simply, just passing a pair of DMax()
and DMin() functions.

Hope that helps


Tim F
 
G

Guest

Steve and Tim,

Thanks for your replies. Works perfectly with Steve's advise. Tim, the
qryBandDateSort just has two fields that are relevant to the procedure, so I
don't think it uses up many resources.

LAF


SteveS said:
LAF said:
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

One thing I see is that you should have a rst1.movefirst before the "Do Until...":

' added the .movefirst line
rst1.MoveFirst
Do Until rst1.EOF
.
.


For debugging purposes, add

Msgbox sSql

after the "sSql = " statement to see if the ID is what you expect it to be.


Now change the line to:

sSQL = "[BANDNUM] = '" & CurrentID & "'"


You said that "Bandnum" is a text field, so "CurrentID" needs to be enclosed by
quotes.



HTH
 

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

Top