No Current Record, BOF, EOF, Absolute Position

[Alex]

I have the following code:

Dim rs As DAO.Recordset
Dim TheLocID As Integer

Set rs = Me.F_LocationsSubForm2.Form.Recordset
If rs.RecordCount = 0 Then
TheLocID = 0
Else
If rs.BOF Or rs.EOF Then
rs.MoveFirst
End If
TheLocID = rs.Fields!LocID ' Error here sometimes
End If

Sometimes I get a "No Current Record" error on the line with "TheLocID =
rsFields!LocID". I am baffled as to why I am getting this error given that I
am already testing for BOF and EOF, and when the error occurs it does even
though both BOF and EOF are False. Furthermore, upon examining the
AbsolutePosition of the recordset it turns out that it's -1. How is this
possible? Given that both BOF and EOF are False, shouldn't the
AbsolutePosition be at least 0?

 

[Van T. Dinh]

I tend not to use RecordsetClone rather than Recordset for this since
RecordsetClone doesn't interfere with the Form operation.

Try:

Dim rs As DAO.Recordset
Dim TheLocID As Integer

Set rs = Me.F_LocationsSubForm2.Form.RecordsetClone
If rs.RecordCount = 0 Then
TheLocID = 0
Else
rs.MoveFirst
TheLocID = rs.Fields("LocID").Value
End If

 

[Klatuu]

Notice the addition of MoveLast. The combination of MoveLast, MoveFirst
ensures the record cound is accurate and the AbsolutePosition is updated.

Set rs = Me.F_LocationsSubForm2.Form.RecordsetClone
If rs.RecordCount = 0 Then
TheLocID = 0
Else
rs.MoveLast <<-----
rs.MoveFirst
TheLocID = rs.Fields("LocID").Value
End If

 

[Van T. Dinh]

However, from the O.P. posted code, he doesn't need the accurate count. He
only checks to make sure the Recordset is not empty.

Hence, I posted without MoveLast.

 

[Klatuu]

I understand your point. My intention was to give the O.P. information that
may help him in the future. It would appear from his post, he did not
understand how to deal with this circumstance.