How do I create a report with a dropdown field just like the form?

[klbrownie]

I am trying to create a report that asks the user to select the report option from a drop down field. The field is based on field in a form, which pulls its options from a table.

Source Table - tblDelayReasons - contains 8 reasons

Form - frmMain Table - users select reason using combo box while entering data into form

Field in Form - Reason if Procedure Delayed

I want the report to ask user to select from same list of reasons. I have tried to create query using criteria below, but query just asks for input for this field.

[forms]![frmMainTable]![Reason if Procedure Delayed]

This using access 2003

[NeoPa]

The only thing obvious is that the name of your form is shown in two ways in your post. I expect your code should work if the names are correct. Otherwise try :

[Forms]![frmMain Table]![Reason if Procedure Delayed]

For further tips (although, as I say, your format looks fine) see Referring to Items on a Sub-Form.

[klbrownie]

My apologies... That was a typo on my part when I was giving the example. The Form name is "frmMain Table" with the space.

(whew! that would have been embarassing if that was the reason!!!)

[NeoPa]

That's why we always suggest most strongly (Think of a point just short of insist for politeness) that you use Copy/Paste when showing us your code. You'd be surprised how often freehand typos do cause the problem. At best they just waste a little time.

In this case, I'm not sure what might be wrong. I still suspect it may be something to do with the name, but as I have no access to the actual form in your database it's hard to check. As I said earlier, the format of the reference seems fine.

[klbrownie]

I think there is something going on with the whole database. I have another issue where something that "should" be working, is not. I have a query just to count all the "yes" values in a checkbox field that is counting every record no matter what I try.

I need a beer!

[NeoPa]

Do you want to Copy/Paste the SQL in here for us to check over for you. Sometimes an extra set of eyes is all that's needed.

[klbrownie]

Sorry it took so long to get back, I was given a "drop everything and do this" project. Those are SO much fun!!!

Ok, I found a way to get around the check bos issue, but I still have my original problem, the drop down on the report. Below is the SQL for a dumbed down version of my query (I removed all the non pertinent fields).

SELECT [Main Table].[Last Name], [Main Table].[First Name], [Main Table].[Reason if Procedure Delayed]
FROM [Main Table]
WHERE ((([Main Table].[Reason if Procedure Delayed])=[forms]![frmMain Table]![Reason if Procedure Delayed]));

Anything else needed to debug this?

[NeoPa]

Let's take a step back here. I may have missed a glaring point earlier.

You want the user to interface with the report itself?

If so then you should understand that this is not supported for Reports.

[Stewart Ross]

As NeoPa has said, reports cannot interact with users. If you want users to be able to select particular reasons etc from a drop-down list you will need to design an unbound form for the user to interact with and populate that with the controls you want the user to make the selections from, and a command button to run the report when the user is ready.

The code behind the command button would be the place to build a Where condition string to apply to the report on open. Filtering a report using a Where condition string is not particularly difficult if you are careful about delimiting string values etc correctly. The Where condition string (like a 'Where' condition in SQL but without the keyword 'Where') is an optional argument of the DoCmd.OpenRepor t method that the command button calls.

-Stewart