subform update issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dominique Schroeder - 28 Jun 2006 16:20 GMT

Hi,

I have a parent form and several subforms that are located on different
tabs.
Subform1 is filtered through a query which selects all records of table1
that have a status field of "open".
The subform2 which is on another tab filters for records on table1 with
status of "completed"

Question: When a user changes the status from "open" to "completed" on the
first subform1, I would like the record to "move" from subform1 to subform2.

I tried requery and refresh on the dirty event of the form with no luck

Here is the code:

I tried the 2 different save record methods below
I tried the requery as well as the refresh
Nothing works!

Private Sub Form_Dirty(Cancel As Integer)

DoCmd.RunCommand acCmdSaveRecord
Me.Requery

'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
'Me.Refresh
End Sub

thanks,

Dominique

Reply to this Message

Allen Browne - 28 Jun 2006 16:35 GMT

Attempting the save the record the instant that it dirties is not going to
be successful. It goes you no chance to perform the editing.

Instead, accept the fact that the subform's won't be updated until the
record is saved, and use the AfterUpdate event of the form to requery the
subforms. Something like this, with your subform names in place of Sub1 and
Sub2:
Private Sub Form_AfterUpdate()
Me.[Sub1].Form.Requery
Me.[Sub2].Form.Requery
End Sub

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I have a parent form and several subforms that are located on different
> tabs.
[quoted text clipped - 28 lines]
>
> Dominique

Reply to this Message

Dominique Schroeder - 28 Jun 2006 21:37 GMT

Hi Allen,

Thanks for the response.

My code issues a DoCmd.RunCommand acCmdSaveRecord command first, which saves
the record; then it requeries;
My users complain that they have to move to the next record to save the
changes they've made to the current record.

I'm open to any other suggestions.

Dominique

> Attempting the save the record the instant that it dirties is not going to
> be successful. It goes you no chance to perform the editing.
[quoted text clipped - 40 lines]
> >
> > Dominique

Reply to this Message

Nicholas Scarpinato - 06 Jul 2006 16:49 GMT

Have you tried having your code issue a DoCmd.RepaintObject acForm "Formname"
after the records are requeried? It sounds like the record is SAVED, but not
REFRESHED on the form. Moving to the next record updates the form, which
repaints it with the requeried data, so a RepaintObject should accomplish the
same thing.

Reply to this Message