onclose event fires when filteron?

[sozzer]

I'd be grateful if anyone knows whether a report onclose event is supposed to fire when i apply a filter to a report using vba. My code opens a report and then applies a filter using whatever criteria a user input into a form. But whenever it does this, the onclose event seems to fire, opening a modal form and making the report inaccessible. it's driving me nuts trying to think of a way round this - help!

many thanks

 

[ZmrAbdulla]

Can pls post the code that you are using for Both Apply Filter & OnClose ?

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[ZmrAbdulla]

Duplicate post thread705-1037131

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[sozzer]

Hi, many thanks and sorry for my delay! Sample code that exhibits this behaviour is below (I dont have the exact code here at the moment that I'm working with)

Private Sub OK_Click()

DoCmd.OpenReport "repTable1"
Reports![repTable1].Filter = "[First Name] = 'Ben'"
Reports![repTable1].FilterOn = False
DoCmd.OpenReport "repTable1", acViewPreview
DoCmd.Close acForm, "frm1"

End Sub


Private Sub Report_Close()

DoCmd.OpenForm "frm1"
MsgBox "Hello"

End Sub

Whenever I click on OK on the form, (and filter) the msgbox "Hello" appears, and frm1 reopens over the report. If I take out the filter command, the close event doesnt trigger.

Thanks again

 

[ZmrAbdulla]

Why do you need OpenReport twice?

Private Sub OK_Click()
[COLOR=red]DoCmd.OpenReport "repTable1"[/color]
Reports![repTable1].Filter = "[First Name] = 'Ben'"
Reports![repTable1].FilterOn = False
[COLOR=red]DoCmd.OpenReport "repTable1", acViewPreview[/color]
DoCmd.Close acForm, "frm1"
End Sub

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[sozzer]

sorry, the first openreport shouldnt be there and is isnt in the real code. It goes:

Private Sub OK_Click()
DoCmd.OpenReport "repTable1", acViewPreview
Reports![repTable1].Filter = "[First Name] = 'Ben'"
Reports![repTable1].FilterOn = True
DoCmd.Close acForm, "frm1"
End Sub

 

[ZmrAbdulla]

I have tried to create the same probel to know what actually it is. And it porduced the problem. So I decided to use OpenArgs to eliminate the probelm. see the code below.
for report

Private Sub Report_Close()
    DoCmd.OpenForm "frm1"
    MsgBox "Hello"
End Sub
'============================
Private Sub Report_Open(Cancel As Integer)
    Select Case Me.OpenArgs
    Case "Myfilter"
        Me.Filter = "[First Name] = 'Ben'"
        Me.FilterOn = True
    Case "AnotherFilter"
        [COLOR=green]'here some arguments[/color]    
   End Select
End Sub

for form

Private Sub OK_Click()
    DoCmd.OpenReport "repTable1", acViewPreview, , , , "Myfilter"
    DoCmd.Close acForm, "frm1"
End Sub

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[sozzer]

Many thanks, but I now have two problems. When I try to do what you have posted above, I get the compile error "Wrong number of arguments or invalid property assignment" on this statement:

DoCmd.OpenReport "repTable1", acViewPreview, , , , "Myfilter"


Also, I have to capture a value on the form to determine the filter, as below:

Private Sub Command1_Click()

DoCmd.SetWarnings False
If IsNull(Me.USERid.Value) Then
MsgBox "You must enter a user name to continue"
Else

Dim MyFilter As String

MyFilter = "RemovedBy ='" & Me.USERid & "'"

DoCmd.Close
DoCmd.OpenReport "repRemoved", acViewPreview

Reports!repRemoved.Filter = MyFilter
Reports!repRemoved.FilterOn = True

End If
End Sub

Any further help you could provide is greatly appreciated.

 

[ZmrAbdulla]

You are not doing what I explained. Anyway, I have made some changes on your code, test it and tell me. See the highligted areas

Private Sub Command1_Click()
    DoCmd.SetWarnings False
    If IsNull([COLOR=red]Me.USERid[/color]) Then
        MsgBox "You must enter a user name to continue"
        [COLOR=red]Me.USERid.SetFocus
        Exit Sub[/color]
    Else
        Dim MyFilter As String
        MyFilter = "RemovedBy ='" & Me.USERid & "'"
        DoCmd.OpenReport "repRemoved", acViewPreview
        Reports!repRemoved.Filter = MyFilter
        Reports!repRemoved.FilterOn = True
	[COLOR=red]DoCmd.SetWarnings True[/color]
        [COLOR=red]DoCmd.Close[/color]	
    End If
End Sub

[ul]
[li] DoCmd.Close must be at the last, you can't send arguments from a closed form [/li]
[li] DoCmd.SetWarnings True You must turn it on after your purpose [/li]
[li] Exit Sub If USERid is blank exit the event and set focus to that textbox[/li]
[/ul]

________________________________________
Zameer Abdulla
Visit Me
The best thing to spend on your child is your time.

 

[sozzer]

Thanks, but i have now given up and made the 'on open' form not modal and shifted focus back to the report. It's less elegant but easier!