Solved Requery underlying form, but retain bookmark (1 Viewer)

zeroaccess

Active member
Local time
Today, 05:03
Joined
Jan 30, 2020
Messages
671
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.
 

zeroaccess

Active member
Local time
Today, 05:03
Joined
Jan 30, 2020
Messages
671
You haven't shown your effort, so generically it would need to reference the appropriate form:

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've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
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.
 

zeroaccess

Active member
Local time
Today, 05:03
Joined
Jan 30, 2020
Messages
671
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.
 

zeroaccess

Active member
Local time
Today, 05:03
Joined
Jan 30, 2020
Messages
671
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

Guest
Local time
Today, 10:03
Joined
Feb 2, 2019
Messages
1,218
Works for me, and I'm going to try one of those methods later.
Strange
Now the message is different.
Seems Japan is banned.:eek:

Is there any way to see the contents?



Screenshot_20200218231336.jpg
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
Code is short
Code:
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

Guest
Local time
Today, 10:03
Joined
Feb 2, 2019
Messages
1,218
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.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
Since you cannot see the link the second thread was about that
Code:
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

Wino Moderator
Staff member
Local time
Today, 03:03
Joined
Aug 30, 2003
Messages
36,118
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

You've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
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.

Code:
'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

AWF VIP
Local time
Today, 06:03
Joined
Jun 21, 2011
Messages
5,901
Strange
Now the message is different.
Seems Japan is banned.:eek:

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.
 

zeroaccess

Active member
Local time
Today, 05:03
Joined
Jan 30, 2020
Messages
671

deletedT

Guest
Local time
Today, 10:03
Joined
Feb 2, 2019
Messages
1,218
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.
SQL:
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

Active member
Local time
Today, 05:03
Joined
Jan 30, 2020
Messages
671
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

AWF VIP
Local time
Today, 06:03
Joined
Jun 21, 2011
Messages
5,901
You say it's not working, exactly what did you put on the button?
 

Users who are viewing this thread

Top Bottom