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

[zeroaccess]

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

As stated, my form is not responding to DoCmd.GoToRec or FindRecord. On another DB, it works as expected. I don't think there are any differences that account for this but it wouldn't be the first time I overlooked something.

 

[GinaWhipp]

Okay, I'll be patient while you look it over.

 

[zeroaccess]

Okay, tested more this morning and the commands are working. Odd. It's possible that one of my C&R or decompiles sorted that out.

Now for the results. The requery method does update the datasheet, but the scroll bar goes back to the top. Scrolling down shows the record position did return to the previous record, though it's not very useful if you can't see it until you scroll.

The refresh method maintains the scroll bar position, but does not update the datasheet. Since this is no different than commenting out all of the code, I would just skip this one.

It doesn't seem that I'm able to requery and keep the scroll bar position, so I'm implementing a manual button in the header to requery, for now.

 

[GinaWhipp]

Hmm, I only tested using a Form and did not have that problem. So, in my spare time (which I have sooo much of NOT) I will look at datasheets.

 

[Xyloz]

Stop. All of you just stop the form.requery madness...

The reason microsoft haven't made a "fix" is because there isn't one to make...

Stop trying to Requery Forms when you only want to Requery RecordSets.

Forms!frmRecordList.Form.RecordSet.Requery

The reason you are struggling is because you are requerying your form. But stop to think what are you really trying to do here...

You are trying to requery the underlying RescordSet the form is relying on so the form will update, and everyone using the front end will see those changes accordingly.

If you have an OnUpdate that runs a query within the form you can even use.

Me.RecordSet.Requery

You will stay in position within the form or ContinuousForms, please don't go down a rabbit hole and start logging scroll bar positions.

I have seen a "solution" to this lack of forethought on this forum where someone actually uses RecordSet function CurrentRecord to log the position of the RecordSet in the form!... To then use it as a Bookmark for the Scrollbar to then requery the form and then use the RecordSet position to rescroll the form and the facepalm is real in the words of UncleRoger "Hiiiyaaaa MajP Hiiiiyaaa looking at you over-engineering like a boss".

I mean sure you can do that using SelTop

Rec = me.CurrentRecord
Me.Form.Requery
Rec.Seltop

SelTop... But still what's the point in that just Requery the record set am I missing something?

I signed up immediately to stop people from doing exactly this convoluted mess. just requery your recordset.

That's not to say don't ever requery forms, there is a time and a place, usually when creating form filters you want to update or refresh both the form and the dataset, then sure you want to use form.requery operator for requerying the form and it's recordset...

But if you stop and think about what you are actually trying to do in this instance... you are only trying to requery your recordset within a form without refreshing the form... so just do that.... the solution becomes really apparent.

Me.Recordset.Requery.

 

[MajP]

Somebody hitting the wacky weed? Because this is the strangest rant I have ever heard. Nothing in here makes any sense. Sounds like you used one of those random sentence generators. I ran it through the gibberish generator and seems to make more sense now. Thanks for joining the forum I am sure we will get a wealth of knowledge from you.

Set functions.
I have and it's then a "soluted update trying then use form an OnUpdataset as a "fix" is lack of UncleRoger "Hiiiyaaa MajP Hiiiyaaa logging Requery you want every
You only use you are the requery the forms!frmRecords of the underlying thought on of forms, ple a time and even recordSet.Requery trying to down a "soluted updatase changes really to use forms!frmRecordSet.Requery Rec = me.CurrentRecordset, the stop and stop trying Records of forum when you withis lacepalm is lack of you .

 

[Xyloz]

Random GIbberish generator I like it xD almost as much nonsense as recordset scroll bar logging instead requerying the RecordSet xD

- this is the Uncle Roger reference... your solution reminded me of.

"Log the scroll bar and then recall the position of the scroll bar after requerying the form and here is a long list of code.... Hiiiyaaa no no no if you ask me you type Me.RecordSet.Requery and push the button xD"

 

[MajP]

Now I am guessing you are on crack,because I got no idea what you are talking about. Maybe lost in translation. Use the Google translator, it will be much better than your attempts at English, but I applaud you for making the effort. I am sure I could not say a word in your native language.

Now I do not know if you can count, but lets look at the long list of code you reference.

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

Holy cow, that number is 7. Now the message box code is optional so lets call it 5 lines. Dude, trust me I doubt you want to challenge my knowledge of Access and VBA. I probably forget more each day than you will know in your life. However, if you want to help people on this forum like I do, post something that is makes sense. Lets see if you have any knowledge.

 

[zeroaccess]

I will try the recordset requery and report back the results.

 

[zeroaccess]

Well, I'll be damned if it doesn't work.

However, even though the recordset is requeried, the changed field(s) don't change until you leave the selected record using tab or other means. Another step would be needed to make it "instant". The other problem is new records appear in place of the first record, replacing what was there until a requery or recalc.

A refresh of the form after the recordset requery works but causes a 1-second pause when closing the pop-up form, which is when the requery and refresh of the underlying form is invoked. Recalc is about a half second, so a little better. This may take longer if the database is not on your LAN/WAN.

What does work instantly is coding in a requery of specific field names on the record list, but that's a bit cumbersome and would cause a lot of requerying even if no changes are made. That doesn't seem like best practice, but it is faster than the refresh or recalc.

I'm just going to stick with the recordset requery then a form recalc unless and until I can figure out if it's possible to just recalc or requery only one record. But I don't think so.

So to achieve the stated goal here, which is to update a record list in datasheet view (I am using a Split Form) without losing your position when closing a pop-up form, you just need, on the form close event:

If Me.Dirty Then
Me.Dirty = False
End If

Forms!frmRecordList.Form.Recordset.Requery
Forms!frmRecordList.Form.Recalc

Then some error handing in case that form isn't open for some reason. On Error Resume Next works fine but I know some will take issue with it.

There is a performance penalty compared to having users click a requery button but the automatic nature is nice. The other alternative is to requery each applicable field by name.

 

[MajP]

Not sure what the issue is, but if I use this simple code

lngPK = Me.EmpID
Me.Requery
'return form to original record
With Me.Recordset
  .movelast
  .FindFirst "EmpID = " & lngPK
End With

The selected record always ends up at the top of the continuous form / dataheet or for the last few records the at least are visible in the windo.

 

[zeroaccess]

I'll add that I would prefer it to only update the list if there are updates to make, thus saving resources when just viewing then closing a record without making changes. I'm not sure how to do that. Can we detect if there are any changes to the recordset after the requery and before the recalc? I could then just write an if statement.

 

[Xyloz]

Depends how you want to enact the Me.Recordset.Requery

AfterUpdate is the usual standard for most interfaces, especially if you have multiple users querying at the same time.

You can define it on keypress and everything will update as you type

Press F4 to open properties and under the Events tab it lists all events you can encode the Me.RecordSet.Requery on. Press the three dots and select Code builder and paste in the code on what ever event you like and have a play.

It's only one short line of code and so long as your recordset isn't huge it's pretty fast.

Also depending on where and how you requery affects how the records set interacts.

It won't for example apply an external filter but, from a pop up it will refresh the data in any form or subform and I use it quite regularly for continuous forms to maintain your place while filling out pop ups and the like.

Also Conditional Formatting is not affected by this as it is a logical operator not dependant of a refresh of the form so conditional formatting will also update

me.dirty is a good tool to use to avoid conflict errors and parent.dirty is also a good thing to use in subforms if they enact on a parent form.

 

[Xyloz]

RajP 5 lines vs 1 to do the same thing.
Or 18 actions the CPU has to do vs 3 for each call of that function.

That's n3 vs n18 in computing...

Which runs more efficiently? Saving face is nice and all but amitting when you have gone down a rabbit hole is a time saver especially if a rubber ducky comes along to point it out.

Lets examine that work around for a second

Form.Requery requeries the Form ( the query the form is reliant on is run) and the form resets

RecordSet.Requery just requeries the record set (the Query the form relies on is run on the recordset) and the recordset resets. The form does not but because it is reliant on the recordset it updates and focus is maintained.

Justifying using more code is not good practice, targeting the form and not the data is not good good practice. I am not talking about a code off here.

I am talking about recognising what you are trying to do and doing it and not going down a rabbit hole to engineer a workaround to something that already exists.

Regarding zeroaccess.

If you want to do things on change. F4 and set the After_Update to

me.recordset.requery

You may need to declare me.dirty = false or Parent.Dirty = false

You don't have to do the requery on the close of the popup you can just do it on each editable field After Update.

 

[Minty]

@Xyloz Before getting into a discusion you will probably lose, I would have take a look at some of @MajP sample databases and code.

As a new member here with 5 posts you may find it wise to have a degree of respect for someone who possibly has forgotten more eloquent code than you have written. Just my 2 cents...

 

[zeroaccess]

If you want to do things on change. F4 and set the After_Update to

me.recordset.requery

You may need to declare me.dirty = false or Parent.Dirty = false

You don't have to do the requery on the close of the popup you can just do it on each editable field After Update.

I will try the on change first, thanks. I don't see how an after update on the record list fields would work as they wouldn't be updated to trigger anything. Come to think of it on change probably wouldn't work, either. I want to act based on a change to the recordset.

 

[zeroaccess]

One method could be to use my last modified date on the form. If modified >= date - 0.5, then do the requery and recalc. Otherwise, just close the form. That way just viewing records older than 12 hours ago is still fast.

 

[Xyloz]

One method could be to use my last modified date on the form. If modified >= date - 0.5, then do the requery and recalc. Otherwise, just close the form. That way just viewing records older than 12 hours ago is still fast.

You could opt for OnKeyPress if your dataset is lean enough.

@Minty Your probably right sorry MajP... Wasn't digging at you just at the example of the length of code used for a work around to something that already exists.

Plus you can't google that solution, it requires some quite in depth knowledge of the access system.

My advice on most things is in code builder using Ctrl+j it's good practice to book one hour of your day to just having a small record set and going through the options and seeing how they work properly.

 

[zeroaccess]

I have this working satisfactorily now:

If Me.LastModified >= Now - 0.01 Then

Do the above.

As stated you could also try a requery of specific fields on the record list instead of the whole recordset. I have not tried that.

But with the last modified and Now optimization only new and updated records will trigger the requery and recalc of the record list on close of the pop-up form. Otherwise it closes instantly when no changes are made.

While there is a short pause when it happens, it is working well so I will consider this solved. Thank you @Xyloz for signing up here and helping with this and I hope you get along better with the others.

 

[Xyloz]

I have this working satisfactorily now:

If Me.LastModified >= Now - 0.1 Then

Do the above.

As stated you could also try a requery of specific fields on the record list instead of the whole recordset. I have not tried that.

But with the last modified and Now optimization only new and updated records will trigger the requery and recalc of the record list on close of the pop-up form. Otherwise it closes instantly when no changes are made.

While there is a short pause when it happens, it is working well so I will consider this solved. Thank you @Xyloz for signing up here and helping with this and I hope you get along better with the others.

Glad to help I only signed up to point that out.

Often what we think we are doing is not actually what we told the system to do and it always pays off to take a second to stop and examine our actual tradjectory vs intended direction.