Report filter / on close

[lostwithaccess]

Hi everyone,

I am having a few problems with a report which is filtered by a form which I have been unable to find a solution for.
My report has an On Close event to open my main menu form, but this is being triggered by my filter command, ie- applying the filter seems to close and reopen the report, with the 'close' bringing up the main menu. Does anyone know a way to stop this?

My filter code is:

Option Compare Database

Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmReportFilter"
End Sub

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "rptInstitutions"

If Nz(Me.cboCountry, "") <> "" Then
strSQL = "txtCountryName='" & Me.cboCountry & "' AND "
End If

If Nz(Me.cboInstitute, "") <> "" Then
strSQL = strSQL & "txtInstitutionName='" & Me.cboInstitute & "' AND "
End If

If Nz(Me.cboLastName, "") <> "" Then
strSQL = strSQL & "txtLastName='" & Me.cboLastName & "' AND "
End If

'Strip Last " And "
If strSQL <> "" Then

strSQL = Left(strSQL, (Len(strSQL) - 5))
Debug.Print strSQL
'Set the Filter property
DoCmd.OpenReport stDocName, acPreview
Reports![rptInstitutions].Filter = strSQL
Reports![rptInstitutions].FilterOn = True

Else
MsgBox "No criteria specified, opening the report unfiltered"
DoCmd.OpenReport stDocName, acPreview
End If

Exit_cmdFilter_Click:
Exit Sub

Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click

End Sub


My report code is:

Option Compare Database

Private Sub cmdReopenFilter_Click()
DoCmd.OpenForm "frmReportFilter"
End Sub

Private Sub Report_Close()
DoCmd.OpenForm "Menu", acNormal
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportFilter"
End Sub


Many thanks

 

[boblarson]

Create a variable in a standard module:

Public blnNotOpenMenu As Boolean

Then, in the code for your filter, put in the start of it
blnNotOpenMenu = True

and then in the report's Close event have it check to see if blnNotOpenMenu is false and if it is then let it run the code to open the menu. (remember, you don't need to have the Else in there as there is no Else to worry about). Then, at the end of the report's Close event have it set

blnNotOpenMenu = False

so that it is all reset and back to being able to either be set to true by the filtering or that it is false which will let it open the menu.

 

[lostwithaccess]

Thanks Bob,

I have had a good go at making this work but I am fairly new to access, especially coding, and have not managed to make it work yet.

I made a new module:

Option Compare Database

Public blnNotOpenMenu As Boolean
----------------

Put this at the start of my filter:

Option Compare Database
blnNotOpenMenu = True
-------------------

And added this to the On Close event:

Private Sub Form_Close()
If blnNotOpenMenu = False Then
blnNotOpenMenu = False
End Sub
--------------

I am getting a bit lost as to taking it further / making it actually work. Would you be able to confirm the code above is correct? I am not sure how to follow the following points in terms of writing code -

- and then in the report's Close event have it check to see if blnNotOpenMenu is false

- and if it is then let it run the code to open the menu.

- Then, at the end of the report's Close event have it set blnNotOpenMenu = False

Many thanks

 

[boblarson]

I made a new module:

Option Compare Database

Public blnNotOpenMenu As Boolean

Yes, that is correct.

----------------

Put this at the start of my filter:

Option Compare Database
blnNotOpenMenu = True
-------------------

No, that is INCORRECT. It should be INSIDE an event not under the Option Compare Database part.

And added this to the On Close event:

Private Sub Form_Close()
If blnNotOpenMenu = False Then
blnNotOpenMenu = False
End Sub
--------------

No, I said REPORT'S On Close event because that was what you were concerned with, not the form. And the Report's On Close event would look like:

Private Sub Report_Close()
If blnNotOpenMenu = False Then
   DoCmd.OpenForm "MenuFormNameHere"
End If
blnNotOpenMenu = False
End Sub

 

[lostwithaccess]

Thanks Bob,

The menu now only appears when the print preview is closed, rather than every time the filter is applied. I also had to change my open report command button to open in acViewPreview rather than acViewReport