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

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

Can you post the xTab SQL ?

mx

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");

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 ?

?

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.

Do you want a copy of the data base file?

copy - yes

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

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

WEIRD !!!!

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 ?

Don't just say Weird and then leave me here!

Dude ... I'm not.  Trying everything I can think off ... stand by.

mx

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

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

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

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

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);

Select allOpen in new window

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

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

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

Select allOpen in new window

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

> 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°)

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

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.

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.

"Sorry about the technical comments hijacking this thread; "
Can't say I am :-)

mx

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

Marcus knows 'stuff' :-)

mx