By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

VBA Return a Value from Docmd.OpenForm to a Report

P: 16
I have a report that when it opens it will propt DoCmd.OpenForm which will open a form that will filter the results of the report. The problem is that if you close the form without initializing the filter it will return an error because the report fields will not be defined.

What I would like to do is on a Form_Close event return a variable to the Report_Open event which will cancel the creation of the report.

Any ideas?
Dec 7 '11 #1

✓ answered by ADezii

  1. Create a Public Function that will let you know if a Form is Open, and if it is Open, is it Open in Form View:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fIsFormOpen(strFormName As String) As Boolean
    2. Const conFORM_VIEW As Byte = 1
    3.  
    4. fIsFormOpen = False     'Initialize
    5.  
    6. If (SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0) Then    'Form is Open
    7.   If Forms(strFormName).CurrentView = 1 Then      'Form is Open in Form View
    8.     fIsFormOpen = True
    9.   End If
    10. End If
    11. End Function
    12.  
  2. In the Open() Event of your Report, Call the Function, pass to it the Name of your Form, then act on the Return Value:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2.   If fIsFormOpen("Some Form Name") Then
    3.     MsgBox "Form IS Open"
    4.   Else
    5.     MsgBox "Form NOT Open!"
    6.   End If
    7. End Sub

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
In this case, it would probably be better to open the form first, enter the filter, and then have a button that opens the report with your filter. That way, if you decide that you don't want to open the report, then you can just close the form and you don't have to worry about the report.

If you want to keep with how you have it, then you would just put the following code in the form OnClose event:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Close acReport, "{report name}"
Dec 7 '11 #2

P: 16
Great idea, but that does not work in this case because the report must be initiallized in order to reference the controls which are being change.

I did find a messy solution where I put a textbox on the report itself that is used as a check if the filter ran.
Dec 7 '11 #3

Seth Schrock
Expert 2.5K+
P: 2,931
I'm not exactly sure what you are trying to do where my idea wouldn't work, but as long as you came up with a solution, that is all that matters.
Dec 7 '11 #4

ADezii
Expert 5K+
P: 8,597
  1. Create a Public Function that will let you know if a Form is Open, and if it is Open, is it Open in Form View:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fIsFormOpen(strFormName As String) As Boolean
    2. Const conFORM_VIEW As Byte = 1
    3.  
    4. fIsFormOpen = False     'Initialize
    5.  
    6. If (SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0) Then    'Form is Open
    7.   If Forms(strFormName).CurrentView = 1 Then      'Form is Open in Form View
    8.     fIsFormOpen = True
    9.   End If
    10. End If
    11. End Function
    12.  
  2. In the Open() Event of your Report, Call the Function, pass to it the Name of your Form, then act on the Return Value:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2.   If fIsFormOpen("Some Form Name") Then
    3.     MsgBox "Form IS Open"
    4.   Else
    5.     MsgBox "Form NOT Open!"
    6.   End If
    7. End Sub
Dec 7 '11 #5

100+
P: 759
Hi all ! ...

... and Hi ADezii !
May I ask you why is better to use the public function ?
Why not test if the form is open under Report_Open event ?
I think that you have good reasons but I can't see its.

Thank you !
Dec 8 '11 #6

ADezii
Expert 5K+
P: 8,597
@Mihail:
Why not test if the form is open under Report_Open event?
You can, of course, keep the Code In-Line in the Report's Open() Event, but in my opinion it is not as efficient, and is definitely not Portable.

P.S. - You also have the option of making the Function Private in Scope, and contained within the Report's Code Module.
Dec 8 '11 #7

100+
P: 759
Thank you !
Dec 8 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.