subform update issue

[Dominique Schroeder]

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

 

[Allen Browne]

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

 

[Dominique Schroeder]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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.