Things to look for when a Report mysteriously asks for parameter (1 Viewer)

cnstarz

Registered User
Joined
Mar 7, 2013
Messages
89
TL;DR: Make sure to check conditional formatting rules that reference old field names that are renamed.

Long Story: In my infinite wisdom when I first started creating this database, I created fields named "Date" in some tables and queries. It wasn't until a few weeks that minor bugs started popping up because of my excellent choice in names. This weekend, I finally decided to just come into work to rename all the fields called "Date" throughout all my tables and queries. After a few hours of going doing that and updating all my forms, queries, reports and vba code, I was finally done! Everything appeared to work smoothly except for one report that was still asking for a Parameter for a field called "Date".

First thing I checked was the Record Source of the report. I checked the query (which is actually a Union Query) to ensure it wasn't the query asking for the parameter... nope the union query opens fine. Just to be safe, I also checked the two queries that the union query combines. I checked all fields for both queries, filter and order by settings for both queries, checked all criterias for both queries, I even checked the Parameter Settings for both queries. Nothing was referencing a field called "Date".

Okay, it must be something with the report then. I checked the filter and order by settings for the report, I checked the control source for all fields, I checked the VBA code to make sure there wasn't anything there referencing a "Date" field. Nada, nothing. THEN -- a lightbulb turned on! I got up and turned it off because I dont like wasting electricity. Also, I must have ghosts in my house. Actually, I remembered about Grouping and Sorting! HA FOUND IT -- not. My Report isn't grouped or sorted. :( :banghead:

I was frustrated. I scoured the intrawebs to see what others had say about the mysterious Parameter being asked about. Most everyone was able to fix their problems by adjusting their Group and Sort because it's a commonly-overlooked feature. Not me though! No, my report is smarter than that. In my frustration, I decided to delete each field one at a time to see if it would stop asking for this stupid parameter. I had one field left and it still asked for the stupid parameter. What are the chances that this last random field is still reliant on this old field name? I deleted the field and the report loaded fine. So, it doesn't ask for the parameter when there's nothing in the details part of the report, but it does ask for it when any of my fields are present. Fhat the wuck, man?

After a few hours of pulling my hair out, I decided to just delete each field and recreate them all. That worked. I recreated all the fields on my report and now it doesn't ask for any parameters. Weird huh?

I came into work this morning only to find out that other things with the database needed addressed that I didn't address over the weekend. Then I got a complain that certain fields on that report aren't changing colors when they're supposed to. Hmmm, conditional formatting is broken for some reason. Ohhhhh, yeah totes forgot to add conditional formatting to those fields after I recreated them. WAIT A SECOND... CONDITIONAL FORMATTING...

None of my new fields had the conditional formatting that the old fields had. I opened up a backup of the database to look at the conditional formatting of those fields... WINNER WINNER CHICKEN DINNER. I had conditional formatting referencing a "Date" field.

So, if you run into this problem where a report is asking for a parameter after you named a field:
- Check all query fields, query parameters, query SQL, query sorts and filters
- Check all Report sorts, groupings, filters, and conditional formatting rules
- Check all Report field record sources, default values, formats, Hyperlink Targets
- Check all VBA code that is referencing the field's old name

Hopefully this post can save someone hours of wasted time. Thank you for reading.
 

RainLover

VIP From a land downunder
Joined
Jan 5, 2009
Messages
5,044
TL;DR: Make sure to check conditional formatting rules that reference old field names that are renamed.
Long Story:
After a few hours of pulling my hair out, I decided to just delete each field and recreate them all. That worked. I recreated all the fields on my report and now it doesn't ask for any parameters. Weird huh?
What you did here was the SMART thing to do. Please read the original post in Full.
Deleting one object at a time may be tedious however you will find the fault.
Just remember to back up often.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,819
Anyone looking for particular expressions in a database should install V-Tools.

The Total Deep Search is very good.
 

statsman

Registered User
Joined
Aug 22, 2004
Messages
2,076
Check your spelling.
It's amazing how often that's the problem.
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,341
The one that gets me is the Order By or Filter By property. I usually set those in code, but during desingn they can get saved.
 

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

Top Bottom