Business Solutions:  Report from Search Results on Form

     By James D. Duncan, CPA      October 2006

            Two months ago we designed a query that we could give a parameter and get a list of matching items. Last month we added a form that a user could use without confusion.  This month we will build a report from that form that called the query.

            Here is the form from last month.  This form let the user enter what text they wanted to search for.  What we will do is build a report that uses the query as the source.  Then we will put a button on this form to run that report.

            First we will create a report using the report wizard.  Move to the report section.  Select new report and we will let the report wizard help us.  Choose the report wizard and choose the query that we used last month.

Click on the OK button.  Then we will move all the fields over to the Selected Fields side.  We can do this by pressing the >> button.  Then press the Next button.

I did not do any grouping so just pressed Next on this screen.

I used LastName and FirstName for the sort order.  You will want to choose what ever fields you want the report to be ordered in.  Then press Next.

I usually do a Tabular layout with Portrait as the orientation.  You can choose what fits your needs.

On the style page I go with corporate.  You can pick and see what style fits your needs.   It will show you as you pick.  Then press the Next button.

I replace the first three letters (qry in this case) with rpt to conform to naming conventions and press the Finish button.

This will then run the query and ask us the following question from that query.  The query does not know what the field on the form is yet so we give it some help. 

Enter an “a” and the following screen appears.  This is the report with the query as its source.  Not pretty and the title and headings need some work but something to see at this stage.

Now, go back to the form and we will put a control button on to call the report.  From the toolbox select the command button.  Click on the form, drag and drop the button on the form.  When you release the mouse the command button wizard starts.  Select the Report Operations and Preview Report and press the Next button. 

Select the report name from the list and press the Next button.

I change the name to have cmd in the front of it.  This tells me this is a command button.

Click on the Finish button and we are ready to run the report from the form.  Probably want to save here.  It is always a good idea to periodically save your work.  Run the form and we have the following.

Enter an “a” in the text to search for field.  Press the Preview Report button and you should get the following.

This is the same as before except no questions about the source of the query this time. Amazing, huh?

            That is all folks!  Nothing to it, when you know how and now you know how.  Practice is the key.  Try new things.  Do not be afraid to try things and do not be surprised when they do not work.  It is called experience.

            Pass it forward.  Help someone and do not tell them.  I like to mow my neighbor’s yard when they are gone on vacation.  I take my edger and tidy up the renter’s front yard when I collect the rent.  Put an extra 10 dollars in the collection plate on Sunday.  Buy the kids fund raisers even from the second kid that comes along.  Wash the dishes for the wife or girlfriend when they don’t expect it or even better do it when they are gone and then do not admit to it.


 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James