Access Report/Query with Prompts and Combo Boxes

[djtech2k]

Hello.

I need to find out how to create a report in access that will prompt a text box for certain values and prompt a combo box to make a selection for other data in the report. Until now, I have written a query with a text prompt for a typed value and then shaped the report around it. I would rather not do that if possible. The combo box idea I cannot figure out at all. Any help appreciated!!!

-DJ

 

[GingerR]

What have you tried in regards to using a combo box?

I'm assuming you have a form, with a text box, a combo box, and a button on it. Sounds like you want users to enter something in the text box, pick something from the combo box, then hit the button and preview the report?

You can use the combo box wizard to put a combo box on your form. It will fill up with data from one of your tables, or from values you type in.

You can use the wizard to put a button on your form which opens the report in Preview mode.

Take a look at the code on the button's OnClick event to see how Access writes the code for you.

Take a look at the properties of the combo box and become familiar with COLUMN COUNT, BOUND COLUMN, COLUMN WIDTHS, and ROW SOURCE properties.

Then your report, instead of having criteria in it's query (i.e. [Enter Date:]) you will instead change the criteria to be something like this:

Forms!FormName!txtDateFrom

Forms!FormName!cboEmployeeID

Without any details from you, this is one basic set up you can look into. There are lots of posts in these forums, and lots of text books, dealing with this issue. So fiddle around a bit and let us know how it goes.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[djtech2k]

OK, well here is what I have done and am looking for:

Until now, I have some queries written with prompts like this: "[Pick This]" in them. If I create a new report and use this query as the backend, when i run the report, I will get prompted for the value just like the query. What I would like to do is:

1) Run a report that will prompt me for a certain field of data. I want the prompt to be a combo list of values (just like a form combo box).

2) I would like to be able to create a prompt within a report for a text box or combo box, but not have to do it from within a query. Like I said, I have gotten a text box prompt to work in a query, but I would rather just do it within the report instead of using a query first.

Does all of that make sense?

The combo box within a report is the priority to me.

-DJ

 

[GingerR]

1)there is no "combo box withing a query." my previous post will take care of this. You will not be "prompted". Instead, you will enter/pick/select values on a form from textbox/combobox/yes-no box/listbox PRIOR to opening the report. Then the report will take those values from the form and not prompt you for anything.

2) A little confused by your wording. If the query works, make the query be the record source of your report. Copy the sql view of your query. Open the report in design view. Paste that sql into the Record Source. Now your report is not based on a saved query, and you can do whatever you want with it.

If I'm off track, then you're going to have to explain more/better. Post the record source of your report, the sql of the query, and any other pertinent information.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

This is how I handle Report Criteria forms. First I build my report(s) and make sure the query gets all of the data I want. My Report Critiera form allows the user to enter data into text boxes, comboboxes, listboxes, etc. so that the user can filter what the report shows. To create the filter (where clause) based on the items entered into the various controls of the Report Criteria form, check out this FAQ faq181-5497 You only have to do 3 things to make it work.

1. Create a new module and copy and paste the functions in the FAQ into your new module
2. Enter information into the tag property of your controls as specified in the FAQ
3. Open the report as indicated in the FAQ