print macro to filter search form

  • Thread starter Thread starter michelleumich
  • Start date Start date
M

michelleumich

Hi all,
I have a search form that finds results by filtering the original
table. It is adapted from http://allenbrowne.com/ser-62.html . All
my search boxes are text boxes on my version. I need to be able to
print the records that the search retrieves. The form is a continuous
form. I know how to set up a print macro if searching with a Query,
but I have many criterion to search for and the filter was the best
choice. Additionally I am having problems with the Query code
itself. Anyways, Is there any way to print out a report of filtered
results like that? Thank you for your time in advance.
M
 
To print the same records as filtered in your form, you can use the form's
Filter as the WhereCondition for OpenReport.

The basic idea is:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End if
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

If the filter includes Lookups associated with combos (applies to Access
2002 and later only), you may need to craft the query for the source of your
report by aliasing the tables so they match the lookup names used in the
filter string.
 
Allen,
Thank you so much for your help. I understand the idea of what you
have given me but I am still relatively new to code and such so I was
wondering where to put this. Would this go in the form code? That is
my best assumption. I have inserted it as such so far:

Private Sub Cmd_Click()
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "QueryMultiValueSearhForm", acViewPreview, ,
strWhere
End Sub


After doing this, how would I configure my Print command button on the
form? Would I set up a macro (attaching it to the command button) to
print the report?
Thank you so much,
M
 
What is the name of your Print command button?

Use that name instead of "Cmd" in the very first line of your code, i.e. the
code can go into the Click event procedure of your button.
 
Allen,
I posted the code you gave me into the click event procedure of the
command button, and I am receiving an error. "access can't find the
macro dim strwhere as string if me.." Did I enter the code
incorrectly into the on click of the command button?
M
 
Set the commnd button's On Click property to:
[Event Procedure]

Then click the Build button (...) beside the property.
Access opens the code window.
The code goes in there.
 
Allen,
Thanks again. Your help worked. However, I have encountered a new
problem. When I search with the form filter and then click my print
command button, I get another error. "Query must have at least one
destination field". I then clicked "Debug" and it took me to this
line of code:

DoCmd.OpenReport "QueryMultiValueSearchForm", acViewPreview, ,
strWhere

Yes, "QueryMultiValueSearchForm" is the title of my report. I'm aware
this is a really really bad name for a report but the logic was to
keep consistent with a line of forms that I had been working with.
(In the process of developing this database I have made over 20 forms
and needed a way to keep them and all their "accessories" straight)
Anyways, Is the error becuase the title of my report is bad? Now that
I know the form associated with this report is the "final copy" I will
probably be changing the Report name anyways. What else could cause
the error?
Additionally, when I received this error I went into the report itself
to see if there was something I did that caused the error (such as
attached a macro to it while working with it earlier that was no
longer needed) and I got the same error "Query must have at least one
destination field." The error is definately in the report.
With this, I tried to make a new report since it is quite obvious
something is wrong with the one I attached it to.
Access is allowing me to create a Report in Design view, but when I go
to make a report with the Report Wizard I receive yet another error
"This feature has been disabled...'follow these steps to enable it'.
" Anyways, I follow the steps that they give and the Report Wizard
has not been disabled! What could I have done to cause this? I don't
really know how to make a report in Design view and the Wizard is much
easier anyways. Again, thank you so much for your help. You are very
concise which I appreciate.
Thanks,
M
 
Allen,
I had an additonal question for you considering I am using an adapted
version of your subform, you would probably be able to help me.
I have changed my tables since creating the adapted form- and I was
wondering if you could help me with re-adapting the form (since I am
still relatively new to Access) to my new tables.
The form I originally based the continuous search form on had
duplicate information- information that was contained in other tables
and all of it had a many-many relationship. It took a while for me to
figure it out, but after only after adapting your subform I finally
figured out how to link the relationships between all of the tables.
Anyways, I ended up moving a lot of the original table into a linking
table- one you would use for a many-many relationship. I was
wondering if you would know how to search for data using the
relationships and with multiple tables. If you need to know how my
tables are laid out more specifically to answer the question (if you
can), let me know. You have been a lot of help already. Thank you so
much,
M
 
1. Open the report in design view.

2. Open the properties box, making sure it's Title reads "Report" (i.e. you
are looking at the properties of the report, not of a text box or section.)

3. On the Data tab, the Record Source property contains the name of the
query the report is using. Go to the Database window, the queries tab, and
open that query in design view. You have no fields in the output grid.
 
Create a query using the tables your fields are now in.

Use that query as the Record Source for your search form.
 
If you need to draw multiple fields from multiple tables, a query is how you
do that.

The example suggests omitting the critiera from the query (i.e. you build
the form's Filter string dynamically).
 
Allen,
Thank you for your help. However, I am still relatively new to Access
(I have been using it only a month and have developed a database over
this summer). I also have no programming background. Because of
this, I do not know a lot of technical terms associated with the
program. What does building the Form's filter string dynamically
mean? I assume "the example" is the one I refrenced in the first
post. Is that correct? Unfortunately becuase of my limited background
I may need a little more guidance to understand what you mean.
M
 
Back
Top