Using a form to filter info on report (1 Viewer)

[LinaRose]

Hello,

I am using a form to filter information so that I can open a report. The report displays information pertaining job costs. Each month the company I work for records their labor, costs, and travel in a form with a year to date amount. Right now, I am having a problem opening up a report for one specific job and a specific month. So what I want the dialogue box to do is to pick from a list of jobs then pick from a list of months, click a button that prompts a preview of the report and voila! Can somebody help me?

 

[databasedonr]

Sure you should be able to do this. I might use combo boxes on the form, then write a query for the report and use parameters in the query based on the values in the combo boxes.

 

[Mihail]

No. I can't help you because I have no really information about the database.

 

[LinaRose]

Sure you should be able to do this. I might use combo boxes on the form, then write a query for the report and use parameters in the query based on the values in the combo boxes.

Thank you! My report is already based on a query. Should I just add a field that requires me to enter information as a parameter? Or do I enter the parameters under query design?

 

[databasedonr]

LinaRose, I'd put the parameter in the query. As an example, I create reports for regional operations - and we have four regions: Central, East, North and West. In my query, in the Region Field, I place the parameter like this: [Forms].[frmPrint].[cboRegion]. Then, on my frmPrint form, I have a combobox called cboRegion. The user selects one of the four regions from the combobox, and presses another button that opens the report. The query grabs the parameter, populates the data and loads the report.

 

[LinaRose]

I have two parameters though and when I entered both in an error message popped up and said it was too complicated

 

[databasedonr]

Numerous parameters shouldn't be a problem. You can always test your query before launching the report to make sure it works. If you like, post your query here and I'll see if I can spot the problem.

 

[LinaRose]

AHA! I figured it out! I put the parameter in the wrong field.

Thank you so much for your help it's finally working!

 

[databasedonr]

Great! Glad to help.

 

[LinaRose]

Well it was working but now in my dialogue box the Month options aren't showing. The parameters work in the query and in the report but the different months are not showing in the dialogue box. I don't understand it worked just a second ago

 

[databasedonr]

Make sure your form is not in Design mode....

 

[LinaRose]

No it's not in design mode. When it's in form view I can choose a Job but not the month

 

[databasedonr]

I'm not sure what's happening ... can you post your database? I'd be happy to look into it but it's hard for me to troubleshoot without seeing it.

 

[LinaRose]

I can't. It has sensitive information on it. Thanks though!

 

[databasedonr]

I understand completely. Did you manage to get it working?

 

[LinaRose]

No. I did figure out that the problem occurs when I put the parameters in the query though. Without the parameters the information shows up, but the report doesn't display the correct data

 

[databasedonr]

There's a couple of things that might be happening.... Are you using a date field for the month parameter, or text? There might be a disconnect then somewhere between your form and the query, too. Try using blank parameters in your query, so in your Job Field in the query, in the Criteria just put in [Enter Job] and do the same in the month field [Enter Month], tie the report to the query and then open the report. It should prompt you for each entry, make sure you're putting relevant data in the prompt box, and see if your report works. If it does, the issue is with pulling the data from your form.

 

[LinaRose]

The parameters work when I open the report and the query. I think the issue is with pulling the data from my form. I tried setting the Record Source to the query and that didn't seem to work. Thanks again for all your help I really appreciate it!

 

[databasedonr]

That sounds like you're 75% there.... Your form can be (and probably should be) unbound, so it has no record source. You can use either text boxes or comboboxes to have the users select data to run the report. So, if you have a combobox, you'll create a source on that to extract Months or Jobs. Then, where your parameter is in the query, change it to pull from the combobox on your form. I gave you an example above. If you get your form to show the data that you want, then all you need is to reference the form in the query and you're good to go.

 

[LinaRose]

This is where I keep running in to problems. I did exactly as you said above. I have the parameters set to pull from the form and the combo box. After I set those parameters the combo boxes stop displaying data. Completely baffled.