Access 2007 report doesn't recognize form text box

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

scotty718

Thread Starter
Joined
Nov 19, 2010
Messages
185
I hope I can clearly explain this problem.

I've built a query that has one a criteria line that looks like this:

IIf([Forms]![frmMyForm]![cbMyComboBox]="Regional",[Forms]![frmMyForm]![cbMyOtherComboBox],[tblMyTable].[myField])

The query runs exactly as it should and filters the value in cbMyOtherComboBox parameter if the word "Regional" is in the noted in cbMyComboBox, or does not filter on anything if the word "Regional" does not appear in the cbMyComboBox.

I've built a report based on this query. In the report I've placed a chart, also based on the same query and created a master-child link on one of the fields so that the chart will loop through the data for each grouping within the field and chart the data.

This all worked great and smooth, until I added the filter to the query that callls the combobox value in the form. While, like I said, the query runs smooth, the report does not. Actually, the report runs fine in report view, but it breaks in print preview (or printing to PDF) and states the following error message:

The Microsoft Office Access database engine does not recognize [Forms]![frmMyForm]![cbMyOtherComboBox] as a valid field name or expression.

Any thoughts on why this won't work with print view (but it does with report view) and how I can fix this?
 
Joined
Jul 29, 2001
Messages
21,334
It is because the query is looking for a field in your table named [Forms]![frmMyForm]![cbMyOtherComboBox] which does not exist.
 

scotty718

Thread Starter
Joined
Nov 19, 2010
Messages
185
Thanks for the reply, Rockn. Any chance you can elaborate, because your answer seems vague to me.

As I stated, the query runs perfect and filters on the value in the combobox and returns the right records. It's the report that is not finding that value when it tries to print.
 
Joined
Jul 29, 2001
Messages
21,334
The report for some reason is interpreting the [Forms]![frmMyForm]![cbMyOtherComboBox] reference as a field value in a table. Is the report using the same query that you can run manually to populate itself with?
 

scotty718

Thread Starter
Joined
Nov 19, 2010
Messages
185
yes, it is. That is why it is baffling.

In case this helps, the report's record source is the same query that run's fine on it's own. Also, the chart that is located in the report has a row source based on that same query, as well.
 
Joined
Jul 29, 2001
Messages
21,334
It may well just be a field name is incorrect on the report. The report fields pull the data based on the querys column heading. Look at the field on the report that contains the data from [Forms]![frmMyForm]![cbMyOtherComboBox] and see if there is a reference error. That field in the query should be something like Expr1 or whatever you aliased that expression as.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
It is quite possible that you might have to set the Criteria as a Parameter, this is necessary with Crosstab queries, so maybe your report needs the same thing.
You could try using a VBA generated SQL statement to set the QueryDef instead of using the Forms![formname]![fieldname] version.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top