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

Posted on 2009-04-17
Last Modified: 2013-11-28
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?


This may not be a cause, but I recently loaded Virtual studio 2005 on the two machines where the problem is happening.
Question by:phmurphy
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Try using the same name for the both linking Fields.

    Author Comment

    Sorry, I don't understand.

    Author Comment

    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.

    LVL 74

    Accepted Solution


       " 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:

    CustID (Primary Key)

    OrderID (Primary Key)
    CustID (Foreign Key)

    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.


    Author Comment

    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.

    Author Closing Comment

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    OK great,

    I am glad I helped.



    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now