requery delema

[BJZeak]

Sorry in advance ... although I have years of programming experience, my VBA time has been minimal

Have an ancient 1997 app (converted to 2003 and running in 2003, 2016, and 2019 versions of Access) that uses conditional formatting to color rows in its child subform control ... a timer control on the main/parent form is used to update both the Date/Time in the form caption as well as automatically update any color changes (multiuser db with dynamically changing details will reflect changes by changing row colors without a user having to manually refresh the form)

At issue and why I am looking for some help with this is:
when there is either more then one page of data within the subform OR the user moves off the first row, the subform control requery (me!mysubform.requery) doesn't maintain the currently selected row in the control.

Is there an acceptable/preferred method to ensure the selected row stays selected and hopefully in the same location on the screen?

I read online that some people are saving the current recordset key value before refresh then refinding it after the refresh ... others state they are using line number ... VBA is not finding me!mysubform.linenumber as a valid method ... and not certain what to use for accessing the recordset directly for both getting and refinding the row ... I find looking at the objects available under "me" in the vba locals window can sometimes take one down a long rabbit hole

There are other controls on the main form so focus could also be on a subform row or a parent control ... so perhaps I also need to save/restore focus to the control as well during the Timer Requery Event

I envision at a high level the following logic
Timer event
Save which Control is in focus
save current subform row/Record or line or key value or ???
possibly save screen location of current row
refresh subform control
restore subform row
possibly restore row position on screen
restore focus

 

[CreativeStrat]

Hi

I envision at a high level the following logic
Timer event
Save which Control is in focus
save current subform row/Record or line or key value or ???
possibly save screen location of current row
refresh subform control
restore subform row
possibly restore row position on screen
restore focus

Well as no one has had a stab at this, and I admit to some confusion about what is intended perhaps something like this may work.


Timer event
Save which Control is in focus - Send control name to an invisible field on the main or sub form wont matter too much as long as you know where it is.

save current subform row/Record or line or key value or ??? - save the ID or primary key to an invisible field as above wherever suits

possibly save screen location of current row - I am unsure of how to approach this

etc

What I think maybe a less cumbersome approach is a no code approach as such. Rather use the conditional format tool for the form and simply load it up with the conditions directly. Sure you can probably do this in VBA and it may be neater, however it may be as effective to simply accept what is and use the simplest approach.

Hope that helps.

 

[strongm]

Bookmarks may be of u se here