form dialog box to run a report

[ksl23]

I have searched the forums and faqs. I read the FAQ Use a form and query to send criteria to a report.

I followed along and I think I am close. My problem is that my report does not run with any data in it. If I print the form field on the report, I see that the value it's sending is the automatically created key number rather than the field value.

Ex. My table called Category
ID Outcome
1 Category 0
2 Category 1
etc

I created a form to select the Outcome. It's a drop down box. I created the query to reference this value yet the report will show 1 (If I selected Category 0) and it will show 2 for Category 1. I'm not sure what I'm missing.

Thanks in advance for your help.

 

[coco86]

if you're using
forms!formname!comboboxname
try using
forms!formname!comboboxname.column(1)

 

[ksl23]

Thanks for responding. I may have misunderstood but I tried adding the column reference. I get the following error:

Invalid bracketing of name'[Forms]![Category Dialog]![WhichCategory.Column(1)]'

I must have messed up the syntax someplace. Can I have the () in side the []?

Thanks again.

 

[coco86]

just get rid of the last set of brackets

[Forms]![Category Dialog]!WhichCategory.Column(1)

 

[ksl23]

Sorry to be a pest. I tried to remove the brackets in my query design but it put them back in on it's own.

Then I went to the SQL view of the query and removed them there.

Now I get the following:

Undefined function '[Forms]![Category Dialog]![WhichCategory].Column in expression'

It seems no matter which way I use, the brackets end up back in there.

I'm leaving soon for the day so don't worry about rushing to respond. I appreciate your patience and help.

Kris

 

[coco86]

You're right, that doesn't work. Sorry about that.

Is there a reason for including the ID number in the combobox? (are you using it anywhere?) If there isn't a need for it, set the recordsource to "select outcome from category" and go back to using [Forms]![Category Dialog]![WhichCategory] in the query. If there is a need, I'm not sure how to go about getting the info. I didn't realize the column property wasn't available.

 

[ksl23]

Thanks. I've looked and I'm not sure where you put in the record source. The ID field is the one that's automatically generated by Access so it has a key.

I think I may have fixed it. What I did was the following:

I right clicked on the combo box and went to properties.
On the Data tab it had:
Row Source Type: Table/Query
Row Source: SELECT [Category].[ID], [Category].[Category] FROM Category;
Bound Column: 1 (I changed this to a 2 and everything seems to work)

I was just looking at it and thinking that maybe this control tells it which column to take the values from. I don't know if this is right but thought it was worth a try and it appears to work. What do you think? If I'm off base just let me know. I really, really appreciate your help.

Kris