Use form to run query through a report.

[ljoekelsoey4]

In access 2007, much like a form that runs a query, I'd like to be able to run a report in between that, so typing in required criteria in a form, it then runs the query through a report. The reason I'm wanting to do this is that I want to be able to insert other buttons into the report, and format it.

I've tried setting up a report that runs the query and then using a form to launch the report, but when I click launch, it asks for the parameter values to be entered. Ideally I want the inputted information in the form to be used as the parameter value to be searched through the report.

Is this possible?

 

[sneuberg]

If you make the query the record source of the report you can provide the form information to the query as criteria by adding form references to the criteria of the query. Alternatively you can put the criteria in the WHERE condition argument of the DoCmd.OpenReport.

 

[Cronk]

"I want to be able to insert other buttons into the report"

You cannot have buttons in a report. However, in the Open event of the report, you could open a form in dialog mode for the user to input the parameters.

 

[ljoekelsoey4]

You cannot have buttons in a report.

I've got a 'close', 'new report', and 'print' button seemingly working!

 

[Cronk]

Yes, I was wrong about command buttons on the report.

You can set the sql data source for the report on the Open event procedure of the report.

 

[ljoekelsoey4]

I'm afraid I don't really understand how to implement what you guys are talking about. Running query criteria through a form is working grand, but I'm having trouble with reports now. creating a report and setting query control source to the report, its only asking me for the first parameter (there are 2 I need to be user-accessible) and its resulting in zero records.

Fundamentally, I'll need to change the criteria I have written in now anyway, but how to get the report to run the query successful is troublesome, and on top of that I believe I need to set the control source of the query to the report but then the entering of parameter values needs to come directly from the combo boxes in the form? I'm a bit stuck.

 

[Minty]

What code have you used? It is a very common process in Access to open a report from a form using data from the form to filter the underlying reports query.

 

[ljoekelsoey4]

I've not input any code, i've been using the expression builder, which doesn't seem enough. Not even sure under what heading the code needs to be actual put under

 

[Minty]

So how are you opening the report? Normally you would add a command button to the form with your query criteria on it, with the onClick event set to do something like

DoCmd.OpenReport "YourReport", acViewPreview

 

[sneuberg]

If you can run the query that has the form reference from the form without problems you should be able to assign the query as the record source of the report (Report Properties, Data tab, Record Source) and run the report from the form but at this point I'm confused about your confusion so I suggest you upload your database so that we can see what we are talking about. Tell us which form, query, and report you are having the problem with.

 

[ljoekelsoey4]

I deleted and rebuilt the report, linked the command button to open report, that was linked to the query, and it worked. No idea what I've done differently, but I'll be sure to make a note of all the settings I've for this.

Thanks for your help guys, I'll be back for help when it inevitably goes t**s up again, cheers!