Determine which form called the report - so I can reopen on close of report

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
Looking for code to determine which form called the report - so I can reopen on close of report.

What is the proper syntax I have two different forms that can call the report?  I need to return to that form on close of the active report..

Forms names are FE_ITPR and FE_ITPR_Enovia.

The current code is asking the user if they wish to return to the previous form or back to the Main Menu.  This is why I need to determine which form was the last active.

Thanks,
k
Private Sub Report_Close()
    Dim frm As Form
    DoCmd.Minimize
    
    Select Case MsgBox("Click Yes if you wish to return to previous form." _
                       & vbCrLf & "" _
                       & vbCrLf & "Click No, to return to Main Menu." _
                       , vbYesNo Or vbQuestion Or vbDefaultButton1, "What do you want to do?")
     Case vbYes
 If Forms![FE_ITPR_Enovia]![ITPR Detail].Form.Visible = True Then
        frm = Me.Parent.active
        If frm.Visible = False Then
            frm.Visible = True
        End If
 Else
        If Forms!FE_ITPR.Visible = False Then
            Forms!FE_ITPR.Visible = True
        End If
 End If
    Case vbNo
        If Forms!FS_MSB.Visible = False Then
            Forms!FS_MSB.Visible = True
            DoCmd.Close acForm, "FE_ITPR", acSaveYes
        End If
    End Select
    DoCmd.Restore
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
If you're using VBA to open the report you can use the OpenArgs property to identify the form that called the report like this:-

DoCmd.OpenReport "Yourreport", , , , , "YourForm"

Then, when you close the report, just reference the openargs property again when the form is closed:-

docmd.openform Forms(OpenArgs)

And that should pretty much do the trick.
Matt
Karen SchaeferBI ANALYST

Author

Commented:
how do I modify the code to inlcude the form name?

    DoCmd.OpenReport "R_ITPR_Release", acViewPreview, "", "", acNormal,
Karen SchaeferBI ANALYST

Author

Commented:
      DoCmd.OpenForm Forms(OpenArgs), acNormal, , , acFormEdit

I am getting wrong data type error.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Karen SchaeferBI ANALYST

Author

Commented:
here is what I have so far

I tried setting a variable to pass the form name - without success.

also

DoCmd.Close acForm, Forms(OpenArgs), acSaveYes
 
Was not successful - did not like the syntax.

any Ideas?

K
Private Sub Report_Close()
    Dim nFrm As Form
    DoCmd.Minimize
    
        Set nFrm = Forms(OpenArgs)
   Debug.Print nFrm
    Select Case MsgBox("Click Yes if you wish to return to previous form." _
                       & vbCrLf & "" _
                       & vbCrLf & "Click No, to return to Main Menu." _
                       , vbYesNo Or vbQuestion Or vbDefaultButton1, "What do you want to do?")
    Case vbYes
        Forms(OpenArgs).Visible = True
    Case vbNo
        If Forms!FS_MSB.Visible = False Then
            Forms!FS_MSB.Visible = True
            DoCmd.Close acForm, Forms(OpenArgs), acSaveYes
        End If
    End Select
    DoCmd.Restore
End Sub

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
how do I close using the OpenArgs - what is the proper syntax?

K
Top Expert 2016

Commented:
open the report with this


DoCmd.OpenReport "R_ITPR_Release", AcView:=acViewPreview, OpenArgs:=Me.Name


Private Sub Report_Close()


    Select Case MsgBox("Click Yes if you wish to return to previous form." _
                       & vbCrLf & "" _
                       & vbCrLf & "Click No, to return to Main Menu." _
                       , vbYesNo Or vbQuestion Or vbDefaultButton1, "What do you want to do?")
    Case vbYes
        Forms(me.OpenArgs).Visible = True
    Case vbNo
 
    End Select
    DoCmd.Restore
End Sub
Karen SchaeferBI ANALYST

Author

Commented:
Thanks, how do I close using the OpenArgs - what is the proper syntax? on the case vbNo


    Case vbNo
        If IsFormLoaded(Forms(OpenArgs)) = True Then
            DoCmd.Close
        End If
        If IsFormLoaded(Forms!FS_MSB) = True Then
            Forms!FS_MSB.Visible = True
 '           DoCmd.Close acForm, nrm, acSaveYes
        End If
    End Select
    DoCmd.Restore
End Sub

K
Top Expert 2016
Commented:
post the codes that you used to open the reports..
are you hiding the form that calls the codes to open the report?


Private Sub Report_Close()
if me.openargs & ""<>"" then

    Select Case MsgBox("Click Yes if you wish to return to previous form." _
                       & vbCrLf & "" _
                       & vbCrLf & "Click No, to return to Main Menu." _
                       , vbYesNo Or vbQuestion Or vbDefaultButton1, "What do you want to do?")
    Case vbYes
        Forms(me.OpenArgs).Visible = True
        ' Forms(me.OpenArgs).setFocus
    Case vbNo
       docmd.close acform, me.openargs
    End Select

end if
 
End Sub
Karen SchaeferBI ANALYST

Author

Commented:
thanks that did the trick.

Karen

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial