EOF Not Working Properly

[Dan]

I presently have the following code that registers when
its not at the EOF, but doesn't work when at the last
record.

Here is the code:

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("qryMainForm",
dbOpenDynaset, dbSeeChanges)
If MyTable.EOF Then
MsgBox "You've reached the end blah blah"
Else
DoCmd.GoToRecord , , acNext
End If


mytable and mydb are publicly named.

Any suggestions would be greatly appreciated.

Thanks,

Dan

 

[Wayne Morgan]

EOF doesn't occur until you move beyond the last record, so it won't
indicate when you're at the last record. You may want to use
AbsolutePosition and RecordCount instead. Remember that AbsolutePosition is
zero based and that to get an accurate RecordCount you need to fully
populate the recordset first (i.e. do a MoveLast then a MoveFirst or where
ever you want to start from).

 

[Brendan Reynolds]

Each time this code runs, it opens the recordset, which sets the pointer at
the first record in the recordset. Therefore (assuming that the query
returns at least one record) MyTable.EOF will never be true.

 

[Guest]

Wayne,

Thanks for the help. I'm currently using AbsolutPosition
to see if I'm on the first record, with this code:
Me.RecordsetClone.Bookmark = Me.Bookmark
If Me.RecordsetClone.AbsolutePosition <> 0 Then

DoCmd.GoToRecord , , acPrevious

but not sure how to combine the movefirst, movelast,
recordcount and absolutPosition to see if I'm on the last
record. I've attempted to use the movefirst and last
since you're message, but not sure if its correct.

Again, many thanks.

Dan

 

[Wayne Morgan]

I think Brendan caught the problem. Although the variable is a global
variable, so it is remembered each time you go through the code, you are
overwriting it within the code each time the code runs.

The MoveLast, MoveFirst is something you would do to the recordset right
after you open it. You can't get an accurate RecordCount, to know what the
last record is in order to use AbsolutePosition, until the recordset is
fully populated. Doing a MoveLast will force this. You then do a MoveFirst
to go back to the first record and start looping through.

 

[Wayne Morgan]

PS.
Before you assign the OpenRecordset to the object variable, check it first
to see if it is Nothing.

If MyTable Is Nothing Then
Set MyTable = MyDB.OpenRecordset("qryMainForm", dbOpenDynaset,
dbSeeChanges)
End If

 

[Dan]

Wayne,

Got it. Thanks for your help.

Dan