requery and return to the same record

[gizzu]

I have a form in spreadsheet view. Basically all I want is to update the information which is viewed, but Form.ReCalc doesn't requery the database. So I tried Form.Requery. But after doing Form.Requery, the first record of the form becomes the active record, and in some cases I have to scroll to see the record that was active before the requery. Is there any way to avoid this?

One possible I solution might be to requery that form and return to the record which was active before the requery. Here comes another problem. The form has a subform in spreadsheet view (which is viewed by clicking + to the left of any record in the first form). If I expand an record to view the sub form, it is also expanded after the requery, but I also want the main form to scroll down so the subform is fully visible (or as visible as it was before the requery), or alternatively, scroll down so the top most record in the main form that is visible also is the top most visible record after the requery. If this is not possible, maybe there is a way to make the record which was the active record before the requery to be the top most visible record after the requery.

Could someone point me in the right direction? Give any suggestion?
Any help is appreciated.

 

[TheAceMan1]

How are ya gizzu . . . . .

Try this ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim rst As DAO.Recordset, pkName As String, pkHold
   
   Set rst = Me.RecordsetClone
   
   pkName = "[purple][b]Your PrimaryKeyName[/b][/purple]"
   pkHold = Me(pkName)
   
   Me.Requery
   rst.FindFirst "[" & pkName & "] = [red][b]'[/b][/red]" & pkHold & "[red][b]'[/b][/red]"
   Me.Bookmark = rst.Bookmark
   
   Set rst = Nothing[/blue]

[ol][li]If the PrimaryKey is [blue]numeric[/blue], remove the single quotes [red]'[/red].[/li]
[li]If you receive and error and the debugger stops on [blue]rst As DAO.Recordset[/blue]:
The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.[/li][/ol]

See Ya! . . . . . .

 

[TheAceMan1]

Woops! . . . .

Sorry [blue]gizzu[/blue] the code I provided won't work . . . . this will:

[blue]   Dim rst As DAO.Recordset, pkName As String, pkHold
   
   pkName = "[purple][b]Your PrimaryKeyName[/b][/purple]"
   pkHold = Me(pkName)
   
   Me.Requery
   
   Set rst = Me.RecordsetClone
   rst.FindFirst "[" & pkName & "] = [red][b]'[/b][/red]" & pkHold & "[red][b]'[/b][/red]"
   Me.Bookmark = rst.Bookmark
   
   Set rst = Nothing[/blue]

See Ya! . . . . . .

 

[gizzu]

Hello! I was running the code from the sub form, so I couldn´t use Me..., but after tweaking the code to fit my needs, it brings the functionality I was looking for. I also wanted the subform to be fully visible, so I inserted Form_themainformname.recordset.movelast before going to the original record, and now it works like a charm. Thank you!