Goto Record on Datasheet

[wrightyrx7]

Hi all,

I have a datasheet, when a record is clicked it opens in my VIEW/EDIT form. The record is changed and the user clicks "save and close". Which saves to the table just fine.

However, it does not update the datasheet with the new value. And when i run code to 'requery' the selected/highlighted record goes back to the FIRST/TOP record.

From what i have read this is not the normal behavior of Access. But im done trying to figure out why it is doing it.

So now i need some to figure out the code that runs when the VIEW/EDIT form closes......remembers the selection before the requery then selects/highlights the record again after requery.

Please can someone help.

THanks
Chris

 

[June7]

In my experience, Refresh/Requery of form always sets focus to FIRST/TOP record.

Google: access requery form return to record
Here is one https://bytes.com/topic/access/answers/206042-requery-return-record

 

[JHB]

Have you read my question here?
https://access-programmers.co.uk/forums/showthread.php?t=293560

 

[Frothingslosh]

First off, as a rule, double-posting will actually result in limiting the number of people helping you, so I would suggest just sticking to this thread.

Now, that said, requerying will always move the pointer to the first record. My first suggestion would be to use Refresh instead of Requery - that updates all records in the current recordset to reflect any changes that have been made, but doesn't actually recreate the recordset.

If you still wind up at the first record, then what you're going to have to do is save the location (typically by storing the Primary Key in a variable), run the refresh or requery, and then using either a filter or recordset navigation to display the saved record.

 

[wrightyrx7]

First off, as a rule, double-posting will actually result in limiting the number of people helping you, so I would suggest just sticking to this thread.

I thought i was doing the right thing by splitting the post. The post linked by JHB was me trying to find a fault my form/s without using VBA, so i posted it in the 'Forms' part of the forum. However, the fault was intermittent and could not replicate the fault to give JHB the info.

So i got to the point of giving up and I came to the VBA part of the forum to request some 'VBA' code.

I didn't know this would be frowned upon. Im just doing my part to keep the forum organised.

Now, that said, requerying will always move the pointer to the first record. My first suggestion would be to use Refresh instead of Requery - that updates all records in the current recordset to reflect any changes that have been made, but doesn't actually recreate the recordset.

If you still wind up at the first record, then what you're going to have to do is save the location (typically by storing the Primary Key in a variable), run the refresh or requery, and then using either a filter or recordset navigation to display the saved record.

Appreciate the info above i didnt know Refresh did not recreate the recordset. However, when i used refresh it still ended up at the first record.

So i have gone with your second suggestion by reading through the link that June7 kindly posted i have used the below code on the 'On Close' event of the VIEW/EDIT form.

Dim RecId As Long

Application.Echo False
RecId = [Forms]![frm_main].ID

[Forms]![frm_main].Form.Requery

With [Forms]![frm_main].RecordsetClone
   .FindFirst "ID" & "=" & RecId
   [Forms]![frm_main].Bookmark = .Bookmark
   [Forms]![frm_main].RecordsetClone.Close
End With

Application.Echo True