Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2119
  • Last Modified:

Enter parameter value prompt in ACCESS 2007 Report but not in 2003.

I have a strange behaviour that wasn't occuring a week ago.  I have a database with a report that now gives me an "Enter Parameter Value" prompt for the field "ID1" which is the primary key that the query for the report is based on.  The exact same database doesn't have this prompt on another machine that is running ACCESS 2003, but does have the same issue on another machine with ACCESS 2007.  I actually copied the file to the other computer to make sure they were the same.

The second page of the report is linked as a child to the main form using ID1 from the main form and ID4 from the child.


Any ideas why this is happening in 2007 and not in 2003, and how can I make it stop?

Any ideas why this would change and what is causing it?

Thanks,
Pat

This may not be a cause, but I recently loaded Virtual studio 2005 on the two machines where the problem is happening.
0
phmurphy
Asked:
phmurphy
  • 4
  • 3
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Try using the same name for the both linking Fields.
0
 
phmurphyAuthor Commented:
Sorry, I don't understand.
0
 
phmurphyAuthor Commented:
Are you saying that I should change the name of the ID4 field on the child to ID1?  If so, I just tried this.  I changed the field name to ID1 in the child report page.   It didn't work, but why would ID4 work in 2003, and not in 2007, as well as why would it work last week but not this week?

By the way, the child report page is based on a query of two tables that are linked on ID3 and ID4.  Both of these are Primary keys.

Pat
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Jeffrey CoachmanMIS LiasonCommented:
phmurphy,

   " It didn't work, but why would ID4 work in 2003, and not in 2007, as well as why would it work last week but not this week?"

If you check many of the questions here, you will find scores of posts where askers state that code that worked fine "Years", now does not work.

This can be caused by anything from corruption to, Service packs, to differences in office versions.

My suggestion was just that, ... a suggestion.

Most of the time the linking field will have the same name, and be a little more desciptive than just "ID".

For example:

tblCustomers
CustID (Primary Key)
CustName
CustDOB
CustCountry

tblOrders
OrderID (Primary Key)
CustID (Foreign Key)
OrderDate
OrderFreight
OrderStatus

Notice here that the Primary and Foreign key is "CustID" in both tables.
(Not ID1 in tblCustomers and ID4 in tblorders.)

My thought was at least to rule this out, because Access will (If you have the "AutoJoin" feature turned on) link fields with the same name by default, in queries.
So my guess wat that it may have linked two fields with the Name ID1 behind the scenes.
Either that or Access 2007 might have "tightened" up it tolerance of linking fields with different names.

Basically the Parameter prompt means that Access cannot find the Field, so it is asking you.
It can occur when Fileds are Deleted, Renamed, ect.

I have also had it happen when the query is not "Optimized" or when I insert a parameter before saving it.
In this case I simply rebuild the Query from scratch, or copy the underlying SQL to a new query.
Then run the compact/Repair utility.

JeffCoachman
0
 
phmurphyAuthor Commented:
Thanks,
I really appreciate the examples you provided of ways this can happen.  I deleted the existing queries and rebuilt them.  You reminded me that if I delete or add a field, the queries get messed up unless you reconstruct them.  I don't think I changed any field names, but the query rebuild was a good idea.

Just rebuilding the querywasn't enough however, I had to re-select them by getting into design view for the report, then select the data tab and reselect the query, and save.  Everythibng worked fine,

Just doing that wasn't enough however, because when I closed the database and reopened, everything was lost.  I had to redo it and then recompile.  Now it is all locked in.

You help has saved me a lot of agony.
Thanks,
Pat
0
 
phmurphyAuthor Commented:
Thanks.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK great,

I am glad I helped.

;-)

Jeff
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now