Solved

Parameter Value Box keeps popping up when creating a report in design view

Posted on 2008-06-24
29
444 Views
Last Modified: 2013-11-28
I'm trying to create a report for a crosstab parameter query in Access 2003. As soon as i start to create the text boxes, the parameter value box opens. I can put parameters in or just cancel, it doesn't matter. The box just opens again and again..What am I missing?

Juanita
0
Comment
Question by:jmflemming
  • 13
  • 12
  • 4
29 Comments
 
LVL 75
ID: 21861561
This is happening when you add a text box to the report in design view ?

Can you post the xTab SQL ?

mx
0
 

Author Comment

by:jmflemming
ID: 21861590
I almost forgot how to access the SQL. Is this what you want?
PARAMETERS [Please enter Start Date] DateTime, [Please enter End Date] DateTime;
TRANSFORM First([Health Stream].Class_Name) AS FirstOfClass_Name
SELECT [Health Stream].Classroom, [Health Stream].Start_Time, [Health Stream].End_Time
FROM [Health Stream]
WHERE (((Format([Class_Date],"Short Date")) Between [Please enter Start Date] And [Please enter End Date]))
GROUP BY [Health Stream].Classroom, [Health Stream].Start_Time, [Health Stream].End_Time
ORDER BY [Health Stream].Classroom, [Health Stream].Start_Time, Format([Class_Date],"Short Date")
PIVOT Format([Class_Date],"Short Date");
0
 
LVL 75
ID: 21861615
yes.

Is it asking for

   [Please enter Start Date]   and or                 [Please enter End Date]

?

and This is happening when you add a text box to the report in design view ?

?
0
 

Author Comment

by:jmflemming
ID: 21861628
Yes. It alternates between the two.  And, it almost just keeps it stalled. I had to quickly close the box and then close the report before the box opened again.
0
 

Author Comment

by:jmflemming
ID: 21861637
Do you want a copy of the data base file?
0
 
LVL 75
ID: 21861659
copy - yes

Can you zip up the MDB, then attach the file for upload here  ... removing any sensitive data of course?  
0
 

Author Comment

by:jmflemming
ID: 21861674
How about I just send you a copy of the data base file however it is...There isn't any sensitive information. I don't know how to do all that stuff you're talking about.
Schedule-Database1.mdb
0
 
LVL 75
ID: 21861769
WEIRD !!!!
0
 

Author Comment

by:jmflemming
ID: 21861782
So, did I do something to make it behave this way? Or, am I attempting to do something that the program can't do? or ?
0
 

Author Comment

by:jmflemming
ID: 21861785
Don't just say Weird and then leave me here!
0
 
LVL 75
ID: 21861797
Dude ... I'm not.  Trying everything I can think off ... stand by.

mx
0
 
LVL 75
ID: 21861837
Man ... I don't know.  There is something wrong with the Crosstab itself - some sort of corruption.  If I open a new report, no controls ... then as soon as I make 'Weekly_Schedule_Crosstab' the Record Source pop up, I get four parameter pop-ups followed by an error message ... see attached.

The ~sq_rWeekly Schedule is an embedded SQL statement that seems to be 'missing' - not even listed in MSysObjects.  

I Imported all objects into a new mdb - same problem.  Compact & Repaired and Decompiled - same problem.
The Xtab query itself seems to be corrupted.  Try recreating the xtab from scratch ... then report back. I'm headed home - back later.

Error.gif
0
 

Author Comment

by:jmflemming
ID: 21862003
OK. I recreated the crosstab. I made screen shots all along the way with a few comments. It still gives the same problems when I try to create the report!
Crosstab-Query.doc
0
 
LVL 75
ID: 21863048
Well, this clearly is an issue with the WHERE clause.  I removed the PARAMETERS part ... and when I run the query, it gives me:

The Microsoft Jet database engine does not recognize '[Please enter Start Date]' as a valid field name or expression'

I MUST ... be forgetting something about WHERE clauses and Xtabs !!!!

mx
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75
ID: 21863071
ok ... I tried this on one of my xtabs - that does not have a WHERE clause ... I added a Between [x] and [y] ...

and I get the SAME problem !!!!!  Seems you can't use a Parameter on a Xtab - but I find that hard to believe!!

I will get someone else looking  at this ...

mx

0
 
LVL 58

Expert Comment

by:harfang
ID: 21864358
Hello.

The repeated parameters come from the background error checking. Choose (Tools | Options), [Error Checking] and disable. Now you get prompted only a couple of times every time Access needs to rebuild the field list (opening, adding a field, saving, etc.), which is still annoying but at least it stops at some point.

The real problem is that you cannot use a cross-tab with variable columns to build a report. Forms and reports need a fixed list of columns, which you get by using the query property 'Column Headings', equivalent to PIVOT ... IN() (see below).

If you really need dynamic columns, it gets quite complicated:

    How to create a dynamic crosstab report in Access 2002
    http://support.microsoft.com/kb/328320

However, from the look of your report, you want to build a calendar, so it makes sense to pivot on the week day instead of the full date. That way, you can build your report using fixed day numbers, which will always exist as columns. You will notice that you can turn error checking back on and that Access will only ask for parameters when actually running the query, not while designing (it doesn't need the parameters to build the field list...).

Also note that your current query does not work on my machine, because it relies on regional settings. Try to avoid using formatted dates or time for anything but display. For example, your columns were not properly sorted, were they?

Cheers!
(°v°)
PARAMETERS

  [Please enter Start Date] DateTime,

  [Please enter End Date] DateTime;
 

TRANSFORM

  First(Class_Name) AS FirstOfClass_Name

SELECT

  Classroom,

  Year(Class_Date) AS Class_Year,

  Val(Format(Class_Date,'ww')) AS Class_Week,

  Start_Time,

  End_Time

FROM

  [Health Stream]

WHERE

  Class_Date Between [Please enter Start Date] And [Please enter End Date]

GROUP BY

  Classroom,

  Year(Class_Date),

  Val(Format(Class_Date,'ww')),

  Start_Time,

  End_Time

PIVOT

  Weekday(Class_Date) In (1,2,3,4,5,6,7);

Open in new window

0
 
LVL 75
ID: 21867278
Thanks Marcus for jumping in.  Right after I signed off last night, I check a book I have and it reminded me that PARAMETERS are required in xTabs - which did exist in the original mdb downloaded - if you are going to have a WHERE Clause that prompts for parameters.  I just couldn't get around that continual prompting, so you explained why that happened.

However, upon initial opening of the report in design view, after only 4 prompts now ... that ends with the error message @  http:#a21861837 - which I still wondering where that comes from.

Too bad that Error Check isn't just MDB specific ... I like the feature in general.

I've done more than one dynamic xTab column scenario on forms and reports ... certainly more complex.

mx
0
 

Author Comment

by:jmflemming
ID: 21868712
Yes, I am creating a schedule (calendar) of classes. I attach a view of the current excel that we use. But, everything has to be checked and rechecked and the possibilites for mistakes is large. I thought I could do this little access program to at least set up a beginning for the scheduler person to assign trainers to the classes. I can get the queries to work. I have a query to list the trainers that are scheduled with their scheduled times off the main trainers schedule. And, I have this one to do the classes. But, it's turning out to be a much bigger problem than I thought and clearly I'm in over my head!

I took out all the data I think from the schedule copy except 6/15-6/21.  I was only going to worry about GSH now.  I thought I would put 2 queries together...The top would be the classes with space for her to assign trainers. And, the bottom a list of trainers for her to choose from.

Juanita
Copy-of-Second-Quarter-2008-Trai.xls
0
 
LVL 58

Expert Comment

by:harfang
ID: 21870956
Joe,

Normally, a query with this type of name, e.g. "~sq_r[report]" is the saved version of the 'record source'. Apparently there must be a conflict between different tasks and one of them needs the compiled (saved) version of the query before it has been validated (meaning both parameters are documented and the field list exists).

About the error checking option, I suppose you could manipulate it with:

    Application.SetOption "Enable Error Checking", False

But globally it's a bad idea, it's better to work with queries having a fixed field list.

Juanita,

It's surprisingly complex to create a good looking calendar report in Access. I often use sub-reports or other advanced techniques involving programming when I need one of them. You might be able to pull it off using the cross-tab I sowed earlier (have you tried it?), although there is one important limitation in cross-tabs: they return only one single value field. In your Excel example, there are two (class name and trainer?), which you would need to concatenate into a single multi-line field.

If you want to obtain all classes for a given week, you could use the week number or the WHERE clause below, which uses a single parameter to extract all days in the same week.

Finally, note that your data isn't well structured. The combined key over four fields makes sense, but you should replace the class name, the classroom and and times with lookup fields from other tables. In the long run, this would make your database more efficient and easier to maintain. For the times, I'd suggest standard time slices, e.g. called "sessions".

Given all that, you could base a main report simply on the list of classrooms crossed with the sessions, yielding one detail section for each possible combination. On that section, you can include five (seven?) times the same sub-report, each with a different link field reflecting the date you want to show. This is one of the 'advanced' techniques I was hinting to.

I don't have a good example at hand. Tell us how the new cross-tab goes and whether you think you can build your report using just that; it's clearly the easiest solution.

(°v°)
WHERE

  Class_Date

    Between [Please enter Date]-Weekday([Please enter Date])+1

    And [Please enter Date]-Weekday([Please enter Date])+7

Open in new window

0
 
LVL 75
ID: 21870983
""~sq_r[report]" is the saved version of the 'record source'."

Well, also ... if you paste in an SQL Stmt for a combo/list box Row Source - or a Form/Report Record Source, then you typically see that in the MSysObjects table.   In this case however, I could not find that specific ~sq .... in MSysObjects - even though it was display in the error message.

"But globally it's a bad idea, it's better to work with queries having a fixed field list."

Well, not sure how that setting relates to queries. But, I like it on because ... for example say on a form (in design view), you have a text box - and lets say for what every reason the Control Source is bogus.  With that setting on and 'Invalid Control Properties' checked, Access displays the little (red) indication with the little drop down list - telling you there is a problem - and the dd list tells you what it is.  There are several other similar places where this is handy.  If you turn of the Enable Error Checking - you lose this feature.  Unless ... may the 'Common Report errors' alone reflects what you are referrring to ?

mx
0
 
LVL 58

Expert Comment

by:harfang
ID: 21871092
> In this case however, I could not find that specific ~sq .... in MSysObjects

In fact, the error message says just that: it cannot find that query. Of course, the message should say something like "time out while reading query (actual query name)" or something else a bit more useful, but here you go. All I was saying was that this naming convention applies to unsaved queries (those where you put the SQL directly in the 'record source' property) and that the message thus issues from the reporting engine while it attempts to build a valid field list.

About the error checking, of course you should leave it on. But as we saw, when you use a cross-tab query, it needs to be actually run to obtain the list of fields. Since it's a parameter query, it will prompt for them repeatedly (while validating the first control, all others become dirty; while validating the second, the first becomes dirty again; etc.)... at some point the engine gets confused and believes the (default) query has been pulled from under its feet. In other words, you need to use cross-tabs with fixed field lists as source for forms and reports. I'm adding that it's good practice anyway: you cannot build anything stable if the fields names change every time.

(°v°)
0
 

Author Comment

by:jmflemming
ID: 21871206
So, I can run a query and then save the results and make a report from the saved query?
I haven't had time to do much with this today....Taught 3 classes. But, I will mess araound a little for the next 1 1/2 hrs. or so.
You guys have really been helpful! But, i won't close this until I can show you what I end up with.

Juanita
0
 

Author Comment

by:jmflemming
ID: 21871345
I ran the query that you gave me. And, it runs ok. I haven't tried to manipulate it into a report yet, though.
I have some responses/questions:

The reason that I was trying to create a report was to allow space for the trainers to be assigned     (added)  I wanted to allow for them to be added by hand so to speak.

 Is it possible to create free space that can be typed into later?

 I was thinking some about maybe changing it to excel or something for the additional info. to be added.

Oh yes, I have been using a book (Access Forms & Reports For Dummies). He talks about subforms. I'll read that and I will definitely look at the How to Create a Dynamic Crosstab... It's going to take me a bit to digest all this.

0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 21871442
" Is it possible to create free space that can be typed into later?'

Sure ... several ways, including just adding a text box with no control source, but with a border = to whatever size.

mx
0
 

Author Comment

by:jmflemming
ID: 21888613
You guys have been very helpfull. But, clearly, this is way over my head!

The idea was convenient, but not practical. I'm afraid that there would be too many changes to the sources where the information for the Healthstream table and she would end up doing all this checking and rechecking anyway.

I looked the article about creating a dynamic crosstab report in Access 2002 and It's too far beyond me. I don't know how to program...I was planning on using the Access functionality and staying out of the SQL, the DAO, and all that.

So, can we just call it done and split the points?

I did it incorrectly last time...so, if I need to do something, you'll need to tell me what & how.
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 250 total points
ID: 21888668
> But, clearly, this is way over my head!

Sorry about the technical comments hijacking this thread; we do that too often...

In a nutshell: if you want to use a cross-tab as source for a report (and avoid all the complexities involving dynamic reports), you need to design it with fixed columns. This is easy in your case, since you basically want the weekdays as headers, which are fixed. In the query at {http:#21864358} I tried to adapt your query to do so, but feel free to experiment yourself.

Key issues:
* use WeekDay([the date field]) as column header
* indicate all possible outcomes in 'column heading' (on the query property sheet)
   (meaning the numbers from 1 to 7)
* use some mechanism to separate the weeks
   - use Val(Format(Class_Date,'ww')) to obtain the week number
   - or use a criteria that selects only one week.

> So, can we just call it done and split the points?

Of course we can, it's your question! In that case, I would suggest that you do not attempt to decide on the usefulness of the answers, but simply split the points among all participants.

In any case, good luck with your project.
(°v°)
0
 
LVL 75
ID: 21888709
"Sorry about the technical comments hijacking this thread; "
Can't say I am :-)

mx
0
 

Author Comment

by:jmflemming
ID: 21892026
"Sorry about the technical comments hijacking this thread; "
I'm not sorry either. I know just enought to understand what you were talking about. It was fun 'ease dropping" so to speak.

0
 
LVL 75
ID: 21892029
Marcus knows 'stuff' :-)

mx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

20 Experts available now in Live!

Get 1:1 Help Now