Filter by form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble passing a filter to a report using a form that contains 5
combo boxes. I have found that the apply filter event results in Acccess
asking for parameter values, even though I have passed the value through the
combo boc. Report filter is set to Yes, paths and names are correct, data
sources are as outlined in Microsoft Knowledgebase example of how to filter
by form. Any help appreciated
 
You could have the form combo boxes act as the criteria
of a query. Then have the report use the query as data
source. It gives you more scope to trouble shoot whether
the criteria are working on the query as expected.

Cheers,
Darren
 
Darren H said:
You could have the form combo boxes act as the criteria
of a query. Then have the report use the query as data
source. It gives you more scope to trouble shoot whether
the criteria are working on the query as expected.

Cheers,
Darren
Click to expand...
Thanks Darren

Could you give me the code for the command button that creates the code to
make the query? I have 5 combo boxes? And may need to deal with some combos
that are not used in building the expression...
 
I may be missing something here. The coding option seems
very complex to get a filtered report.

Say you have a form with 5 combo boxes. Each is "named"
as a control. e.g. cmb1, cmb2,...

I would then create a new query with all the data you
want for your report. Then stick [cmb1],[cmb2] etc in as
the criteria for the data you want filtered.

You create your report using the query as the datasource.

You stick a button on your form to link to the Report in
Preview mode.

This way you can run the query whenever you want to test
that it is picking up the combo box criteria.

Once you get into coding, then I get lost fairly quickly.
If I'm out of my depth and wasting your time sorry!

Darren
 
Thanks Darren

The problem is there may be some controls that are not selected for data to
pass to the query, and would result in returns based on a control with
nothing.
IE Where cboOne = {First Name}
If cboOne is empty, the results will be no records returned as no records
will have no data in [First Name} and I really cannot force user to select an
option, especially if they don't know the correct data to select...it has to
do with null value stuff.
 
Not sure if this will help solve your problem, but I'd like to offer
something that I did (thanks to the wonderful people on this board as well).
Your problem sounds like one I had before where the conditions are way too
complicated to put in a regular query. What I did was manually creating my
SQL Statement and put it in the On Click event of a button (called
"cmdReport") on the form (this form & button are used to bring up the desired
report). I also had an invisible text box on the same form called
"txtSQLStatement".
The On Click event of the button looks something like:

Private Sub cmdReport_Click()
sqlStatement = "SELECT Kits.* FROM Kits WHERE " & Me![cmb1]
Me![txtSQLStatement] = sqlStatement
DoCmd.OpenReport "MyReport", acPreview
End Sub

Of course, you can add more conditions than that to manipulate your SQL
Statement to what you need.

Then I added this statement to the On Open event of my report:

Private Sub MyReport_Open(Cancel As Integer)
Me.RecordSource = Forms.MainForm.txtSQLStatement
End Sub

Hope that helps!
 
Back
Top