ADO Find fails (yet not loaded recordset)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Atlas - 28 Mar 2006 18:16 GMT

Windows XP + Access 2003 + SPs + ADO 2.8 + MS SQL Server 2000

While loading a bound single form, in the open statement i try to find a
given record. Unfortunatelly it fails, because the recordset isn't still
fully loaded.

facts:

1) the table holds about 450 records
2) The behevaiour differs between computers, it looks like slower computers
are affected

I've tried the following

while me.recordset.state <> adStateOpen
loop

but it halts execution (hourglass) until a timeout occurs (many seconds) .
Digging into it throws that state is 9......

Then I'v tried to moveLast, moveFirst and then find

Private Sub Form_Open(Cancel As Integer)

strSQL = " SELECT SOMETHING"

Me.RecordSource = strSQL
Me.Recordset.MoveLast
Me.Recordset.MoveFirst

If globalIDgoto = 0 Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Recordset.Find "[ID Giornale] = " & globalIDgoto, 0, 1, 1
End If

End Sub

But even here on slow computers it fails finding the records in the
recordset.

Tried to change cursrlocation from server to client.... nothin.

What could I try?

Reply to this Message

giorgio rancati - 28 Mar 2006 19:36 GMT

Hi Atlas,

you must wait that the recordset is loaded
----
Private Sub Form_Open(Cancel As Integer)

strSQL = " SELECT SOMETHING"

Me.RecordSource = strSQL

If globalIDgoto = 0 Then
DoCmd.GoToRecord , , acNewRec
Else
Do While Not Me.Recordset.State = 1
Loop
Me.Recordset.Find "[ID Giornale] = " & globalIDgoto, 0, 1, 1
End If

End Sub
----

or
----
Private Sub Form_Open(Cancel As Integer)

strSQL = " SELECT SOMETHING"

Me.RecordSource = strSQL

'In all cases
DoCmd.GoToRecord , , acNewRec

If globalIDgoto <> 0 Then
Me.Recordset.Find "[ID Giornale] = " & globalIDgoto, 0, 1, 1
End If

End Sub
----

bye

Signature

Giorgio Rancati
[Office Access MVP]

> Windows XP + Access 2003 + SPs + ADO 2.8 + MS SQL Server 2000
>
[quoted text clipped - 40 lines]
>
> What could I try?

Reply to this Message

Atlas - 29 Mar 2006 15:35 GMT

> Private Sub Form_Open(Cancel As Integer)
>
[quoted text clipped - 10 lines]
>
> End Sub

Ohhhh YESSS!!!!!!!

Dunno why moveLast & moveFirst didn't give the same result but....... moving
to newrec WORKS!!!!

Thanks Giorgio

P.S. - Where down to such a coding level that I am asking myself why I
didn't go straightfully for a VB.NET + SQL server platform! Especially now
that I'm using custom controls on unbound forms , Janus Gridex to name one.

Reply to this Message

Brendan Reynolds - 29 Mar 2006 11:04 GMT

Try leaving the code that assigns the RecordSource in the Open event, but
moving the code that does the Find to the Load event.

Signature

Brendan Reynolds
Access MVP

> Windows XP + Access 2003 + SPs + ADO 2.8 + MS SQL Server 2000
>
[quoted text clipped - 40 lines]
>
> What could I try?

Reply to this Message