How to check if all the form records are loaded in Access 2003?

[Chunda]

I have an ADP to an SQL database. On a form which shows about 13,000 records
I want to navigate to the last record the current user created, no problem I
just add some code to run in the Load event e.g.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"
On Error Resume Next
Me.Bookmark = rs.Bookmark

I have found that this doesn't work, basically it takes 3-4 seconds to load
the form with data (I can see '...' to the right of the record navigator
bar), which appears to be done asynchronously to the Load event clode.

Hence this works find for records that are near the top of the form, but for
records towards the end, that are not yet loaded when the 'Me.Bookmark =
rs.Bookmark' executes an error occurs.

Is there a way to determine if the form's recordset is completely loaded
before I do the Find operation, or force it to load synchronosly?

If I assign the Find code to a button and manually do it after the recordset
has loaded, or put a delay in the routine for a few seconds all works as
expected.

Any ideas?

Thanks
Chunda

 

[Klatuu]

Add the following to your code. It will cause the recordset to be fully
populated before the Find is executed.
Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
If rst.RecordCount <> 0 Then
rs.MoveLast
rs.MoveFirst
End If
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"

 

[Chunda]

Hi,

This doesn't work, the rs.MoveLast and rs.Move.First execute, but I still
get the run-time error '2001', You canceled the previous operation error,
unless the form has all of the records loaded on the rs.Bookmark line.

Any other things to try?

Thanks
Paul

Add the following to your code. It will cause the recordset to be fully
populated before the Find is executed.
Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
If rst.RecordCount <> 0 Then
rs.MoveLast
rs.MoveFirst
End If
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"

I have an ADP to an SQL database. On a form which shows about 13,000 records
I want to navigate to the last record the current user created, no problem I
just add some code to run in the Load event e.g.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"
On Error Resume Next
Me.Bookmark = rs.Bookmark

I have found that this doesn't work, basically it takes 3-4 seconds to load
the form with data (I can see '...' to the right of the record navigator
bar), which appears to be done asynchronously to the Load event clode.

Hence this works find for records that are near the top of the form, but for
records towards the end, that are not yet loaded when the 'Me.Bookmark =
rs.Bookmark' executes an error occurs.

Is there a way to determine if the form's recordset is completely loaded
before I do the Find operation, or force it to load synchronosly?

If I assign the Find code to a button and manually do it after the recordset
has loaded, or put a delay in the routine for a few seconds all works as
expected.

Any ideas?

Thanks
Chunda

 

[Klatuu]

I have no idea why this is not working. The MoveLast, MoveFirst method is
commonly used to ensure the recordset is fully populated. The error you are
getting does not even seem to relate to the real problem. Just as a test, I
would step through the code and see if the RecordCount of the recordset is
equal to the actual number of records in the recordset after the MoveFirst.

Hi,

This doesn't work, the rs.MoveLast and rs.Move.First execute, but I still
get the run-time error '2001', You canceled the previous operation error,
unless the form has all of the records loaded on the rs.Bookmark line.

Any other things to try?

Thanks
Paul

Add the following to your code. It will cause the recordset to be fully
populated before the Find is executed.
Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
If rst.RecordCount <> 0 Then
rs.MoveLast
rs.MoveFirst
End If
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"

I have an ADP to an SQL database. On a form which shows about 13,000 records
I want to navigate to the last record the current user created, no problem I
just add some code to run in the Load event e.g.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"
On Error Resume Next
Me.Bookmark = rs.Bookmark

I have found that this doesn't work, basically it takes 3-4 seconds to load
the form with data (I can see '...' to the right of the record navigator
bar), which appears to be done asynchronously to the Load event clode.

Hence this works find for records that are near the top of the form, but for
records towards the end, that are not yet loaded when the 'Me.Bookmark =
rs.Bookmark' executes an error occurs.

Is there a way to determine if the form's recordset is completely loaded
before I do the Find operation, or force it to load synchronosly?

If I assign the Find code to a button and manually do it after the recordset
has loaded, or put a delay in the routine for a few seconds all works as
expected.

Any ideas?

Thanks
Chunda

 

[Chunda]

Hi,

Have have created a simle ADP project, with one table of 2 columns (TestId
as int, Text as varchar(50)).

I use the following routine to populate it with 1,000,000 records:

Sub Populate()
Dim n As Long, rs As New ADODB.Recordset

rs.Open "SELECT * FROM tbl_Test", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
For n = 1 To 1000000
rs.AddNew
rs!TestId = n
rs!Text = "The quick brown fox jumped over the lazy dog."
rs.Update
Next n
rs.Close
End Sub

In a form (Single Form) that shows the fields I have the following code:

Private Sub btnTest_Click()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
Debug.Print rs.RecordCount
If rs.RecordCount <> 0 Then
rs.MoveLast
rs.MoveFirst
End If
rs.Find "[TestId] = 999999"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_Load()
Me.MaxRecords = 1000000
btnTest_Click
End Sub

When I run this, I typically get between 400 to 1100 as the record count
property and the recordset doesn't find the record number 999,999. If I wait
about 15 seconds for the form to load all the data and then click the button
it will then find the record.

Any ideas? If I know the record I'm looking for exists I could always loop
repeating the find works, I.m just looking for a more elgant way? Perhaps it
is possible to trap a recordset event that indicates when it has finished
loading the data?

Thanks
Paul

I have no idea why this is not working. The MoveLast, MoveFirst method is
commonly used to ensure the recordset is fully populated. The error you are
getting does not even seem to relate to the real problem. Just as a test, I
would step through the code and see if the RecordCount of the recordset is
equal to the actual number of records in the recordset after the MoveFirst.

Hi,

This doesn't work, the rs.MoveLast and rs.Move.First execute, but I still
get the run-time error '2001', You canceled the previous operation error,
unless the form has all of the records loaded on the rs.Bookmark line.

Any other things to try?

Thanks
Paul

Add the following to your code. It will cause the recordset to be fully
populated before the Find is executed.
Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
If rst.RecordCount <> 0 Then
rs.MoveLast
rs.MoveFirst
End If
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"
On Error Resume Next
Me.Bookmark = rs.Bookmark


:

I have an ADP to an SQL database. On a form which shows about 13,000 records
I want to navigate to the last record the current user created, no problem I
just add some code to run in the Load event e.g.

Dim rs As ADODB.Recordset
Set rs = Me.RecordsetClone
rs.Find "[SpecialID]='" & Nz(sId, "") & "'"
On Error Resume Next
Me.Bookmark = rs.Bookmark

I have found that this doesn't work, basically it takes 3-4 seconds to load
the form with data (I can see '...' to the right of the record navigator
bar), which appears to be done asynchronously to the Load event clode.

Hence this works find for records that are near the top of the form, but for
records towards the end, that are not yet loaded when the 'Me.Bookmark =
rs.Bookmark' executes an error occurs.

Is there a way to determine if the form's recordset is completely loaded
before I do the Find operation, or force it to load synchronosly?

If I assign the Find code to a button and manually do it after the recordset
has loaded, or put a delay in the routine for a few seconds all works as
expected.

Any ideas?

Thanks
Chunda