Command btn to open report based on filtered main form

  • Thread starter Thread starter Asib
  • Start date Start date
A

Asib

I'm relatively new to VBA. But i have adapted Allen Browne's Search form to
my main form. It works great. If I place the command btn on the main form
with this code

Dim strWhere As String
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Old Report", acViewPreview, , strWhere

It works fine. But i have been forced (because of space issues) to create a
popup form to hold all my various report creation command buttons. So i know
why its not working when placed on the new form, obviously the "Me."
declaration. But i cannot for the life of me figure out how to properly
reference the "strWhere" from the main form when opening the report from the
command button on the seperate form. Any assistance is greatly appretiated.
Thank you in advance.
 
I'm relatively new to VBA. But i have adapted Allen Browne's Search form to
my main form. It works great. If I place the command btn on the main form
with this code

Dim strWhere As String
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Old Report", acViewPreview, , strWhere

It works fine. But i have been forced (because of space issues) to create a
popup form to hold all my various report creation command buttons. So i know
why its not working when placed on the new form, obviously the "Me."
declaration. But i cannot for the life of me figure out how to properly
reference the "strWhere" from the main form when opening the report from the
command button on the seperate form. Any assistance is greatly appretiated.
Thank you in advance.
Click to expand...

Instead of using the Me. keyword use the full reference:

DoCmd.OpenReport "Old Report",acViewPreview, ,
forms!MainFormName.filter

In both instances, there is no need to set strWhere = ?.Filter
If there is no filter then that argument will be ignored and all the
records will be displayed.
 
Thanks for the quick response. It works great. But the main form seems to
retain the last filter used. Even after looping through the text boxes and
removeing the filter. The code i am useing for the remove filter button is as
follows:

Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
End Select

Next
Me.FilterOn = False
Me.fltORDNO.SetFocus

even when I close the main form and reopen it, its not actively filtering on
open its just retaining the filter. Im assuming i just need to ad a little
code to clear it out?
 
Thanks for the quick response. It works great. But the main form seems to
retain the last filter used. Even after looping through the text boxes and
removeing the filter. The code i am useing for the remove filter button is as
follows:

Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
End Select

Next
Me.FilterOn = False
Me.fltORDNO.SetFocus

even when I close the main form and reopen it, its not actively filtering on
open its just retaining the filter. Im assuming i just need to ad a little
code to clear it out?
Click to expand...

Your code is not removing the filter, it's just setting the value of
those textbox controls to Null.

That filter can remain. As long as .FilterOn is set to False the form
will not be filtered. You can alternate FilterOn/FilterOff by
right-clickin on the form and selecting either Apply Filter/Sort or
Remove Filter/Sort.

While there is no need to actually remove the Form's filter, you can.
You need to do this in design view if you wish to permanently remove
the filter.

Code an event on the form:

DoCmd.OpenForm "YourFormName", acViewDesign, , , , acHidden
forms!YourFormName.Filter = ""
DoCmd.Close acForm, YourFormName, acSaveYes
DoCmd.Open "YourFormName"

The form will open itself in Design View. The filter will be set to
"". The form will close saving the change. The form will open again in
normal view. The filter property will be blank.
 
Well I Added the (Me.Filter = "")to the OnOpen event of the main form and the
btnRemoveFilter and it seems to be working. I didnt use the opening and
closeing of the form in design view. The code for the Remove filter Button is
now

Private Sub btnRemoveFilter_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
End Select

Next
Me.FilterOn = False
Me.Filter = ""
Me.fltORDNO.SetFocus
End Sub

It still saves the last filter when you open it in design view but access
seems to read it as " " while the form is open. Do you see any reason why
this might fail in a runtime environment or for that matter any use? None the
less it seems to be working correctly. Thanks again for all your assistance.
Much appretiated.
 
Back
Top