Solved Requery underlying form, but retain bookmark

[zeroaccess]

I'm looking at some code from pbaldy here: http://www.baldyweb.com/Requery.htm

I have a record list and a popup data entry form. On the click event of the Close button on popup form, I have a requery, which acts on the underlying form (the record list in this case) to add the new record to the list. Basically, like Northwind.

But if you scroll down the list and open the popup form from one of the existing records, then close the popup form, it will take you back to the top of the list because of the requery.

I'd like to incorporate this bookmark code but I'm not sure what changes would need to be made in this case. Any help would be appreciated.

 

[pbaldy]

You haven't shown your effort, so generically it would need to reference the appropriate form:

Forms: Refer to Form and Subform properties and controls

 

[zeroaccess]

You haven't shown your effort, so generically it would need to reference the appropriate form:

Forms: Refer to Form and Subform properties and controls

That is a great site and I am referencing the form:

Forms!frmRecordList.Requery

I thought I'd have to set the bookmark first before opening the form, rather than on requery. Am I on the right track or overthinking it?

I guess I could set a TempVar on click of one of the records, since those hang around, then on the popup form close and requery, go to that record that is the value of the TempVar with On Error Resume Next. I'm ok with the concept but not so much with the code, so I need to work out how that would look. Or, I could be totally off base here.

 

[MajP]

You cannot use the bookmark. If you requery you get a new set of bookmarks.

Requerying a form invalidates any bookmarks set on records in the form. However, choosing Refresh on the Records menu doesn't affect bookmarks.
Because Access creates a unique bookmark for each record in a form's recordset when a form is opened, a form's bookmark will not work on another recordset, even when the two recordsets are based on the same table, query, or SQL statement.

You need to save the primary key of the record, requery, find the primary key.

 

[GinaWhipp]

Hmm, maybe this work...

Maintain Record Position After Update « Access... entials

access-diva.com

 

[zeroaccess]

You cannot use the bookmark. If you requery you get a new set of bookmarks.

You need to save the primary key of the record, requery, find the primary key.

Thanks. Sounds like I wasn't too far off.

 

[deletedT]

Hmm, maybe this work...

Maintain Record Position After Update « Access... entials

access-diva.com

Your link takes me to a restricted page and a message telling me I can't access the site.

Can you check the link?

 

[zeroaccess]

Your link takes me to a restricted page and a message telling me I can't access the site.

Can you check the link?

Works for me, and I'm going to try one of those methods later.

 

[deletedT]

Works for me, and I'm going to try one of those methods later.

Strange
Now the message is different.
Seems Japan is banned.

Is there any way to see the contents?

 

[MajP]

Code is short

Function fRequeryPlus(frm As Form)
'To use fRequeryPlus Me
    Dim intRecordID As Integer
    intRecordID = frm.CurrentRecord
    frm.Requery
   DoCmd.GoToRecord , , acGoTo, intRecordID
End Function

You can use me.recordset.findFirst "[SomeIDField] = " & me.intrecordiD or the gotorecord

 

[deletedT]

Thanks.
Seems it was as I guessed. Saving the current record index and then use a docmd.GotoRecorde AcGoto.

Edit:
I've used this before. But didn't help me too much. I hoped to find a way to freeze the position too.
If I'm at the bottom of the second page of a continuous form and do a re-query and goto the exact same record as above, the form is scrolled up and although the same record is active, but it's in the top of third page.

 

[MajP]

Since you cannot see the link the second thread was about that

This one will keep record and scrollbar position intact…
Function fRefreshPlus(frm As Form)
'To use fRefreshPlus Me
    Dim intRecordID As Integer
 
    intRecordID = frm.CurrentRecord
    frm.Refresh
    DoCmd.GoToRecord , , acGoTo, intRecordID
 
End Function

 

[pbaldy]

You cannot use the bookmark. If you requery you get a new set of bookmarks.

I'm not clear what this means, as the bookmark code works well as demonstrated in my link.

 

[MajP]

Based on the below

I thought I'd have to set the bookmark first before opening the form, rather than on requery. Am I on the right track or overthinking it?

If they set the bookmark first then requery the bookmarks will not work. That is my way of interpreting what the user was saying. In your method you set the bookmark after the requery so that is fine.
However, I do not see the need for the bookmark IMO. You move to the record in the clone, get the bookmark, then move the recordset. To me seems a lot more logical to just move the recordset.

'set variable to current record ID
lngPK = Me.EmpID
Me.Requery
'return form to original record
With Me.Recordset
  .FindFirst "EmpID = " & lngPK
  'if your key field is text use this line instead
  '.FindFirst "EmpID = " & Chr(34) & lngPK & Chr(34) 
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
   End If
End With

 

[GinaWhipp]

Strange
Now the message is different.
Seems Japan is banned.

Is there any way to see the contents?

Hmm, sorry about that was getting a lot of SQL injections attempts at one time and never removed the restriction. You should be good to go now.

 

[deletedT]

Hmm, sorry about that was getting a lot of SQL injections attempts at one time and never removed the restriction. You should be good to go now.

Million thanks

 

[zeroaccess]

Hmm, maybe this work...

Maintain Record Position After Update « Access... entials

access-diva.com

I could not get either of these to work, no matter what I tried.

The reason is because GoToRec isn't working for me (not next, last, anything). This is a split form in datasheet view - and I tried Screen.ActiveDatasheet.Setfocus to no avail.

I also tried .Findrecord and that didn't work, either.

 

[deletedT]

I could not get either of these to work, no matter what I tried.

The reason is because GoToRec isn't working for me (not next, last, anything). This is a split form in datasheet view - and I tried Screen.ActiveDatasheet.Setfocus to no avail.

I also tried .Findrecord and that didn't work, either.

Strange. It works for me even in split forms.
Can you post a copy of your form?

I wrote the following function, (copying from @GinaWhipp) and call it from different forms.

Function fRefreshPlus(frm As Form)
    'To use fRefreshPlus Me
    ' From : https://access-diva.com/blog/?p=452
    Dim intRecordID As Integer
 
    intRecordID = frm.CurrentRecord
    frm.Refresh
    DoCmd.GoToRecord , , acGoTo, intRecordID
 
End Function

 

[zeroaccess]

Before I can get to that point I need to get GoToRec working on this form. I made a button with that as the only line of code and it isn't working. Once I can figure out why, I'll roll the solution into the procedure.

Oddly, it works when I try it on another DB at home. Will test more tomorrow.

 

[GinaWhipp]

You say it's not working, exactly what did you put on the button?