Subform Requery Doesn't Show Updates to Data

[Len-DSInc]

I've recently converted a database from Acc97 to Acc2003. In the previous
version there was a form that had 2 subforms. A popup form was used to edit
information displayed on the subform. When you closed the popup form, a
requery is called on the main form and the subforms so the updates to the
data would show. However, in the new version, the updates do not show
immediately after closing the popup form.

Here's the code in the popup form that used to work just fine in Acc97:
Forms("frmemployee_main").Controls("subfrmDayOffVacation").Requery

Based on some postings I've changed it this way and it still doesn't work:
Forms("frmemployee_main").Controls("subfrmDayOffVacation").Form.Requery

I've found if I change the data in the popup form. Wait about 10 seconds,
close the popup(which executes the requery), I will see my edit. If I do not
wait, the edit only shows half the time. Has anyone come accross this before?

 

[Len-DSInc]

Marsh,

Thanks for quick the reply, I tried all of those suggestions, still not
working. Here's the code that I have in the Save and Close commands of my
popup:

Private Sub cmdSave_Click()
Me.Dirty = False
DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents
Me.Requery
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Requery
End Sub

Private Sub cmdClose_Click()
If Me.Dirty Then Me.Dirty = False
DBEngine.Idle dbRefreshCache
DoEvents
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Requery

Is it possible I'm running into a record locking problem since the subform
and the popup are pointing to the same records? I didn't think so since I do
not allow edits or record selectors in the subform. Thanks,

Len

:
End Sub

 

[Marshall Barton]

Thanks for quick the reply, I tried all of those suggestions, still not
working. Here's the code that I have in the Save and Close commands of my
popup:

Private Sub cmdSave_Click()
Me.Dirty = False
DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents
Me.Requery
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Requery
End Sub

Private Sub cmdClose_Click()
If Me.Dirty Then Me.Dirty = False
DBEngine.Idle dbRefreshCache
DoEvents
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Requery

Is it possible I'm running into a record locking problem since the subform
and the popup are pointing to the same records? I didn't think so since I do
not allow edits or record selectors in the subform. Thanks,

Get rid of the DoCmd.RunCommand acCmdSaveRecord line.
Dirty = False
does the same thing, but is better because you can be sure
which form's record is being saved.

Get rid of the Me.Requery line. I can not see any point to
requerying the popup form.

About the only other idea I have about the delay is maybe
subfrmDayOffVacation is not getting enough time to display
it's records. Maybe adding this after the Requery will
jiggle things into place:

DoEvents
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Repaint
DoEvents

If you do get rid of the delay, be sure to go back and
remove the various things we're trying one at a time to
determine which one, or combination, is effective. You
don't want any useless code that may be consuming resources.

One other stray thought. The delay may be caused by the
subfrmDayOffVacation form's record source returning a large
number of records. If that's the case, try filtering the
query to the minimum number of necessary records.

 

[Len-DSInc]

March,

Thanks again for your help, I figured out the problem.

I developed this system so long ago in Access 97 I forgot that I setup the
main for a little unorthodox. I did not bind the main form to a query and I
kept the recordsource propery blank. Since it was an HR system, I didn't
want folks to see records they were supposed to, so I chose not to bind the
form until after they logged in. I kept the main form unbound until the user
selected an option, then I programatically bound the form to a query
recordsource based on their selection.

Looks like Access 97 handled the "late-binding" better than Access 2003. I
just made the change, and bound the main form to a query and sure enough I do
not have the problem with the subform that I was experiencing.

Thanks again for all of your help!! It is greatly appreciated. I was
trying to figure this one out for about a week.

Len

Thanks for quick the reply, I tried all of those suggestions, still not
working. Here's the code that I have in the Save and Close commands of my
popup:

Private Sub cmdSave_Click()
Me.Dirty = False
DoCmd.RunCommand acCmdSaveRecord
DBEngine.Idle dbRefreshCache
DoEvents
Me.Requery
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Requery
End Sub

Private Sub cmdClose_Click()
If Me.Dirty Then Me.Dirty = False
DBEngine.Idle dbRefreshCache
DoEvents
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Requery

Is it possible I'm running into a record locking problem since the subform
and the popup are pointing to the same records? I didn't think so since I do
not allow edits or record selectors in the subform. Thanks,

Get rid of the DoCmd.RunCommand acCmdSaveRecord line.
Dirty = False
does the same thing, but is better because you can be sure
which form's record is being saved.

Get rid of the Me.Requery line. I can not see any point to
requerying the popup form.

About the only other idea I have about the delay is maybe
subfrmDayOffVacation is not getting enough time to display
it's records. Maybe adding this after the Requery will
jiggle things into place:

DoEvents
Forms![frmemployee_main]![subfrmDayOffVacation].Form.Repaint
DoEvents

If you do get rid of the delay, be sure to go back and
remove the various things we're trying one at a time to
determine which one, or combination, is effective. You
don't want any useless code that may be consuming resources.

One other stray thought. The delay may be caused by the
subfrmDayOffVacation form's record source returning a large
number of records. If that's the case, try filtering the
query to the minimum number of necessary records.