Requerying Subform and Control Focus

[Scott Ferguson via AccessMonster.com]

I apologize if this has been answered previously. I could not find this
issue.

I have a form with a listbox and a subform. The subform is linked to the
listbox via the "Link Child/Master Fields" properties. The subform updates
as expected when a new item is selected in the list box. I also have
unbound controls and an "Add record" button also on the main form. The
"add record" button is coded to add a the unbound controls' data as a
record to the table that the subform is bound to. After adding the record
to the table, I requery the subform, all through the same on click event of
the "add record" button.

Here is my problem: If a control on the subform has focus when I execute
the "Add Record" button's code (by clicking on the "Add record" button),
the requery does not work. I must perform a "remove filter/sort" to see
the newly added record. However, if the focus is anywhere but in the
subform, say on the listbox for example, when I execute the "add record"
code (again, by clicking on the "add record" button) the requery works and
I see the new record.

I've tried adding this first line before the requery, but to no success.

Me.lstStaff.SetFocus
Me.frmServiceActivity.Form.Requery

I thought clicking on the button in the main form would move the focus off
of any control in the subform, but I guess not. Even still, this does not
explain why this happens? At least not to me.

Any ideas as to why this behavior exists? And what I can do to counter it?

 

[Scott Ferguson via AccessMonster.com]

Sorry...I should probably add that there are NO filters in effect on the
subform. Which puzzles me as to why I must "remove filter/sort" to see a
newly added record.

Scott

 

[Van T. Dinh]

Clicking the "Add Record" button does take the Focus back to the
CommandButton & hence the Main Form.

I am guessing that the new Record is still in cache and has not actually
been updated into the Table and hence you requery does not pick up the new
Record. When you remove the Filter / Sort, the Form (and Subform) is
requeried later which allows time for the new Record to be updated into the
Table and therefore picked up by the implicit requery done by "Remove Filter
/ Sort".

Check Access VB Help on the Idle Method with the option dbRefreshCache.

 

[Scott Ferguson via AccessMonster.com]

Thanks for your response. Your suggestion also made me think of where I
was doing the refresh. I was executing the refresh inside of a transaction
due to another table being updated along with the adding of a record.
Moving the refresh after the .CommitTrans together with a DBEngine.Idle
dbRefreshCache, as shown below, solved the problem.

MyWorkspace.CommitTrans
DBEngine.Idle dbRefreshCache
Me.frmServiceActivity.Form.Requery

Even though the refresh "was" inside a transaction it is curious that it
worked some of the time and not others. Anyway, it works all the time now.

My gratitude to you...

 

[Van T. Dinh]

I never do a (RecordSource) Requery inside a Transaction since everything
inside a transaction is still "pending" AFAIK.