By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,827 IT Pros & Developers. It's quick & easy.

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

P: 25
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
Jan 18 '10 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. [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.
Jan 18 '10 #2

P: 25
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!!!)
Jan 18 '10 #3

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jan 19 '10 #4

P: 25
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!
Jan 19 '10 #5

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jan 20 '10 #6

P: 25
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT [Main Table].[Last Name], [Main Table].[First Name], [Main Table].[Reason if Procedure Delayed]
  2. FROM [Main Table]
  3. WHERE ((([Main Table].[Reason if Procedure Delayed])=[forms]![frmMain Table]![Reason if Procedure Delayed]));
Anything else needed to debug this?
Jan 26 '10 #7

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jan 27 '10 #8

Expert Mod 2.5K+
P: 2,545
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.OpenReport method that the command button calls.

-Stewart
Jan 27 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.