Automatically create report from query

[keeling1]

This should be simple, but I can't figure out how to do it. I created a form-based query. The query works fine. But what I want it to do is automatically create/open a report based on the query. How can I do this?

 

[pbaldy]

You can start with the report wizard.

 

[keeling1]

Hmmm, I've used the wizard, but I don't see an option to automatically generate a report. What I want to do is make the report generate automatically every time the query runs. How can I do this?

 

[pbaldy]

Why? Will the query be returning different fields every time?

 

[keeling1]

Okay, I guess I should explain more. The database is supposed to help my company repair its machines more easily. The machines are divided into categories, and the people fixing them are divided into teams. So, suppose Team 1 wants to know which machines need fixed. They'll use the switchboard to open a form-based query, which will give them options for which team they're in. They'll choose 'Team 1'. Then, I have the query set up to sort the machines and find only those assigned to team 1.

But the query doesn't show the information in a user-friendly way. So I want it to create a report which will show which machines need fixing and what's wrong with them. (All of this has been entered previously.) I have both the query and the report working. But I don't want the user to have to run the query, and then open the report. I want the report to open automatically, so the user can just print it off and go to work. The reason is that I don't want to have to teach every employee how to generate a report from the query.

Thanks, and I hope you can help.

 

[keeling1]

I should add: ideally, the query would open, generate the report, and then close on its own. I don't know how to make it close automatically either.

 

[pbaldy]

It sounds like you want a query that gets its criteria from the form, and a report based on that query. There's no need to be creating either one on the fly. The user only opens the report; the report runs the query itself. Just put a button on the form where they choose their team that opens the report.

 

[keeling1]

Ah! That idea never occurred to me: to make the form open the report, which runs the query, rather than the other way round. Thanks much, pbaldy. The only problem now is that after using the form and clicking OK, the query is asking for parameters. If you know how to fix that, I'd really appreciate help with that too. Either way, thanks again for your idea.

 

[pbaldy]

Asking for parameters by design, or not? I would expect the query criteria to look like:

Forms!FormName.ControlName

As long as the form is open when the query runs, it will should ask for a parameter (it generally will when it can't find something, which usually means something is misspelled).

 

[keeling1]

I would have expected it to ask for paremeters in that way too. That's what it asks when you try to open the query without using the form: [Forms]![frmAnomaly]![cboAnomaly]. But what it's asking is just:

Anomaly

(My query is called qryAnomaly; the report is called rptAnomaly. You can see that my form is called frmAnomaly.) Frankly, I have no idea why it's asking for parameters in this way. If you type nothing and click OK, the report opens and it works correctly. Strange.

 

[pbaldy]

You get that when you open the query directly, or open the report? That tells us if the problem is in the query or the report. What is the SQL of the query?

 

[keeling1]

It happens immediately after I click OK on the form. (The form opens the report, which runs the query.) I'm also posting the SQL of the query called 'qryAnomaly' as you suggested:

PARAMETERS [Forms]![frmAnomaly]![cboAnomaly] Value;
SELECT tblMainDataTable.ID, tblMainDataTable.[Tag #], tblMainDataTable.Name, tblMainDataTable.Date, tblMainDataTable.Team, tblMainDataTable.Machine, tblMainDataTable.[Anomaly Observed], tblMainDataTable.[Anomaly Description], tblMainDataTable.[Corrective Action], tblMainDataTable.[On], tblMainDataTable.Countermeasure, tblMainDataTable.[Assigned to:], tblMainDataTable.[Closed?]
FROM tblMainDataTable
WHERE (((tblMainDataTable.[Anomaly Observed])=[Forms]![frmAnomaly]![cboAnomaly]) AND ((tblMainDataTable.[Closed?])=No));

 

[pbaldy]

If the query runs alone without a parameter prompt, then somewhere in the report is either a control with Anomaly as the control source, or it's in Sorting and Grouping. I don't see a field with that name in the query.

 

[keeling1]

You were exactly right. One of the fields in the form had the wrong control source, and it's now fixed and working perfectly. Thanks for your excellent responses. Maybe I'm pushing my luck, but I've also posted another problem I'm having and haven't gotten any response yet. It's here if you have time:

http://www.access-programmers.co.uk/forums/showthread.php?t=174839

Thanks again.