Requery and Bookmark do not relocate me to current record

[Natha2]

I am having a form in adp (access project with SQL Server as backend).

In order to refresh the form to view data eventualy changed by other users, I have on the form a button with following "on click" event:

Dim varBkm As Variant

varBkm = Me.Bookmark
Me.Requery
Me.Bookmark = varBkm

Data source represents 12682 records, however after clicking, the form's record selector says "Record: 1 of 2251...". (it can be any number, but it is always far below 12682)

It seems that when executing the above code it doesn't reload all the records in the form, resulting in not relocating to the right record.

If I refresh the first 2251 records = no problem.

Refreshing a record bigger than 2251 = relocating on record 2251.

Do you have any other suggestion(s)?

Thanx in advance 'cause I spend already hours in this forum and other ones without result...

Natha

 

[lamarw]

Hi Natha2!

The following is from the Help screen within access:
Requerying a form invalidates any bookmarks set on records in the form. However, clicking Refresh on the Records menu doesn't affect bookmarks.

Does this help?

Lamar

 

[Remou]

Have a look at:
Access 2003 Bookmark ??
thread705-1306335

 

[TheAceMan1]

How are ya Natha2 . . .

Try this (uses the [blue]primarykey[/blue] of the forms recordset):

[blue]   Dim hldID
   
   hldID = Me![purple][b]PrimaryKeyName[/b][/purple]
   Me.Requery
   Me.Recordset.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = [red][b]'[/b][/red]" & hldID & "[red][b]'[/b][/red]"[/blue]

If the primary key is numeric remove the two single quotes [red]'[/red] in [red]red[/red].

See Ya! . . . . . .

 

[Natha2]

Hi TheAceMan1,

I tried your solution and got same result as I described.

It appears that when bookmarking, it takes a time to the form to load the data completely. Very strange.

So what I am doing is looping around until form is completely loaded. (it takes less than half a second)

After much changings and re-changing, I came up with following code that works:

Dim varBkm As adodb.Recordset
Dim a As Integer
Dim RecCountTot As Integer
Dim RecCount As Integer

Set varBkm = Me.RecordsetClone
a = Me.CUST_ID
RecCountTot = varBkm.RecordCount

Me.Requery
SubformAll.Requery

varBkm.Find "CUST_ID=" & a
RecCount = Me.RecordsetClone.RecordCount

          If Not varBkm.EOF Then
            Do
              Me.Bookmark = varBkm.Bookmark
              If RecCountTot <= RecCount Then Exit Do
              RecCount = Me.RecordsetClone.RecordCount
              DoEvents
            Loop
          End If
End Sub

Please feel free to comment.

Thanx to all of you!

 

[PHV]

And what about this ?
Dim a As Integer
a = Me!CUST_ID
Me.Requery
SubformAll.Requery
DoEvents
Me.Recordset.Find "CUST_ID=" & a

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Natha2]

PHV,

No success with your code either...

 

[TheAceMan1]

. . . and this:

[blue]   Dim rst As DAO.Recordset, hldID
   
   hldID = Me!CUST_ID
   Me.Requery
   
   Set rst = Me.reordsetclone
   rst.MoveLast
   DoEvents
   Me.Recordset.FindFirst "[CUST_ID] = " & hldID[/blue]

See Ya! . . . . . .

 

[PHV]

TheAce, I'm not sure that DAO is well suited for an adp.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Natha2]

TheAceMan1,

"Me.RecordsetClone" is not supported by DAO.

 

[TheAceMan1]

Howdy PHV! . . .

Now that you mention I believe your right. Thanks for the heads up . . .

[blue]Natha2[/blue] remove the [blue]DAO.[/blue] from the rst declaration . . .

See Ya! . . . . . .

 

[Natha2]

TheAceMan1,

"Me.Recordset" doesn't support the method "FindFirst".

It returns run-time error '438'.

Anyway, FindFirst will maybe find the right record in Me.Recordset, but it will not position the form to that record. In order to do that you must use Bookmark.

The only problem remaining is the "slow" loading of the form, which I eliminate by looping till Me.RecordsetClone.RecordCount (actual number of records presently contained in my form's recordset after requerying) equals MyBookmark.RecordCount (number of records in RecordsetClone before requerying).