Get Requery to return me to current record

[Prufrock]

I have a form and subform. They are linked by a name field. Once I select the name from a combo box in the main I want the subform to update. I have been getting this to work fine by requerying on exit ( as I tab out of the combo box) except that I go back to first record on requery and then have to click to the last entry.

The requerying forces the name to be fed as the criteria into a query that the subform is based on.

What is the simplest way to get the requery to take me back to the record I am in at the end rather than back to the beginning? The form has a unique number used as the Primary key.

Any help appreciated.

 

[Remou]

If it is a record by position (first, last, next, previous) or record number, you can use DoCmd.GoToRecord. If you wish to find a record by ID, you can use the subform Recordset:

Me.[Subform Control Name].Form.Recordset.FindFirst "ID=" & Name_Of_Saved_ID_Variable_Or_Control

Or there abouts

 

[danvlas]

Try Bookmarks...

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

If the record is always at the end, you could also use DoCmd.GoToRecord


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

. . . example code:

[blue]   Dim sfrm As Form
   
   Set sfrm = [[purple][b]subFormName[/b][/purple]].Form
   [green]'
   'Your code
   '[/green]
   sfrm.Requery
   
   If sfrm.Recordset.RecordCount > 1 Then
      [b]DoCmd.RunCommand acCmdRecordsGoToLast[/b]
   End If

   Set sfrm = Nothing
[/blue]

If you post the code your using it'll give us better insight!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Natha2]

I tried the suggestion of danvlas, without success:

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

 

[TheAceMan1]

How are ya Natha2 . . .

Mixing threads is a bad idea!

Start your own thread, this way you'll have full attention of the forum and others may benefit from your soultion!

BTW: Be sure to have a serious look at FAQ219-2884 or FAQ181-2886

See Ya! . . . . . .