Solved Refresh/Requery Subreport from pop-up

[NauticalGent]

Good morning everyone,

I do not know how something so simple is proving to be so difficult, but here we are. In the attached DB, I have a form called f_OTD_Assignments with a Subreport and also has a subreport. When you click on the "Edit" button, a pop-up/modal form opens and allows the user to change the current status. When the form status is changed, I want the subreports to reflect the change. Should be Childs play but it is kicking the crap out of me.

For the time being I added a "Refresh" button on the top subreport that works but I am trying to avoid that.

I have tried

Forms![f_OTD_Assignments]![srep_OTD_AssigmentDetail].Report![srep_OTD_AssigmentDetail_CLIN].Report.Requery
Forms![f_OTD_Assignments]![srep_OTD_AssigmentDetail].Report.Requery
Forms![f_OTD_Assignments].Requery

No luck so far. I am guessing it could be something to so with both forms being pop-ups but that is simply a guess. Any ideas?

 

[sonic8]

It appears to me there are at least two different bugs in Access coming to light in this small scenario.
First, the Requery, as you've tried it, is ignored,
Second, when using Me.Anything in the subreport, it will raise an error "2467 - The expression you entered refers to an object that is closed or doesn't exist."

Very annoying. - I suggest this workaround:

As you open form f_CLIN_Data modally already, you could use acDialog as WindowMode for OpenForm.
Then run Forms("f_OTD_Assignments").Controls("srep_OTD_AssigmentDetail").Report.Controls("srep_OTD_AssigmentDetail_CLIN").Report.Requery after the form was closed.

The daisy-chain of object references is ugly as hell, but for now I don't see a viable alternative. :-/
It's so dirty, I need to wash my hands after sending this post.

 

[NauticalGent]

Dirty hands I can deal with. Gotta run an errand and then I will give this a shot. Much appreciated!

Edit: Philipp, spot on as usual! Works exactly as I want it to, there is cool and then there is Sonic8 cool! Thanks again...

 

[Pat Hartman]

When you open a popup that will modify data on the current form/subform, then you must open it as Model. That will stop the rest of the code from running in your app until the popup closes. So, the line after the OpenForm method can requery/refresh the current form/subforms.

I'm assuming you meant subform and not subreport.

 

[NauticalGent]

I'm assuming you meant subform and not subreport.

Nope, the form uses a subreport and and then an embedded subreport as well. I went this way based on advice I received on this thread.

 

[561414]

Just commit the record before trying to requery.

Private Sub cboStatusCodeUpdate_AfterUpdate()
    Me.txtExtStatus = Me.cboStatusCodeUpdate.Column(2)
    Me.Dirty = False
'    Forms![f_OTD_Assignments]![srep_OTD_AssigmentDetail].Report.Requery
    Forms![f_OTD_Assignments].Requery

End Sub

Edit:
Or do it from your close button

 

[NauticalGent]

Good morning Philipp, as fate would have it, Mike posted something very similar to this issue this morning.

 

[561414]

Uh, the me.dirty = false before the requery method is the solution to this. Is it not working?

 

[MajP]

@sonic8 and @NauticalGent,
There are two competing things going on that created confusion, and it is something that I do not fully understand.
If you simply remove the requery code in the afterupdate of the combobox in the pop, the below code in the subreport works as expected

Private Sub cmdEdit_Click()
    DoCmd.OpenForm "f_CLIN_Data", , , "[CLIN_ID] = " & Me.txtCLIN_ID, , acDialog
    Me.Requery
End Sub

Or if you remove the above code from the subreport and add the me.dirty = false in the pop up it works

Private Sub cboStatusCodeUpdate_AfterUpdate()
   Me.txtExtStatus = Me.cboStatusCodeUpdate.Column(2)
   Me.Dirty = False
   Forms![f_OTD_Assignments]![srep_OTD_AssigmentDetail].Report.Requery
   Forms![f_OTD_Assignments].Requery
End Sub

But the problem we were seeing is you cannot do both in this case. You cannot requery the subreport from the popup and then try to requery the subreport by self referencing.
The code in the pop-up without the me.dirty does nothing to requery the subreport with new values. However, that requery called from the pop up does something to the sub report instance that I do not understand. It appears as if the instance of the report class goes out of scope while the physical representation does not. So if you also try to call "Me" from the subreport it will now say that the "Object is closed or does not exist". Again if you do not requery the subreport from the pop-up you do not get this problem.
I tried to recreate this to see if it is normal behavior, but could not.
But bottom line avoid requerying the calling object from the called object, and then try to also reference the called object afterwards.

To try to explain this a little further I modified the code in the subreport allowing me to trap the close event of the popup

Dim WithEvents popup As Form
Private Sub cmdEdit_Click()
    DoCmd.OpenForm "f_CLIN_Data", , , "[CLIN_ID] = " & Me.txtCLIN_ID
    Set popup = Forms("f_CLIN_Data")
    popup.OnClose = "[Event Procedure]"
End Sub
Private Sub popup_Close()
  MsgBox "Close"
  Me.Requery
End Sub

I I open the popup and then immediately close it without forcing the combobox to requery the subreport, the msgbox "Close" shows and the me.requery does not error.
If change the value in the combobox which forces the subreport to requery then nothing happens. The subreport class does not trap the close event. So somehow either my "popup" instance in the subreport goes out of scope or the more likely the subreport instance is out of scope.

@NauticalGent, with that said IMO you should always avoid having a pop up reference the calling object to pass values or worse call code or trigger an event. This is bad design

Private Sub cboStatusCodeUpdate_AfterUpdate()
   Me.txtExtStatus = Me.cboStatusCodeUpdate.Column(2)
   Me.Dirty = False
   Forms![f_OTD_Assignments]![srep_OTD_AssigmentDetail].Report.Requery
   Forms![f_OTD_Assignments].Requery
End Sub

Now you have tightly coupled the popup. IMO the popup should know nothing about from where it was called. The calling object should do the work. There are multiple techniques to pull information from a popup without the popup pushing values or causing actions. Trapping the events of the popup, like I demonstrate really helps to decouple your pop ups.

 

[MajP]

then you must open it as Model. That will stop the rest of the code from running in your app until the popup closes.

The above is little confusing in Access because you can build a Modal form which is different from other environments where you can open/show a form modal or modeless. In Access, simply calling any Modal form will Not stop code execution in the calling form. You have to call it with the ACDIALOG value of the AcWindowMode argument. This does both. This opens the form Modal and also stops the code. So this differs for example with VBA Userforms where if you show the form Modal code execution stops in the calling form.

 

[Pat Hartman]

Sorry, I meant dialog.

 

[MajP]

Sorry, I meant dialog

I know what you meant but even that verbiage may still be confusing to novices, because of access being different than other environments (vba Userforms, Vb.net, etc).
Even seasoned Access developers will define a dialog form as any form that is Popup and Modal even if not open with acdialog. Example here

However in this post we are mainly interested in the process of changing the appearance and action of an ordinary form to that of a Dialog Form. So how is this done? The answer to this lies in a number of key form properties. The two most important properties to understand are the POP UP and MODAL properties.

Creating a Dialog Form

Dialog Forms are an integral part of most customized Access Database Applications. As developers, we set them up to enable our application...

ms-access-tips.blogspot.com

I know it is semantics but I know I was confused in the beginning. Access has dialog borders, modal property, and pop up property and none of those interrupt code execution. Which is especially confusing coming from other environments. So being precise "the only way to stop code execution in the calling form is by passing the value Acdialog to the windowmode argument of the OpenForm method."

 

[Pat Hartman]

That would be precise