Particular Crosstab Query Problem (w/ parameters)

[McArthurGDM]

I have a form with a subreport built into it. Here is the picture. (mind you that I am using Tab Controls for the entire Database, but referencing controls isn't an issue)



The command button refreshes the entire database and then makes the control that governs the report visible. I've employed this method before (not with crosstabs) and it works. For the sake of this, this was already set to visible.



As you can see, the report is built off of a crosstab query with 3 parameters (OE, Year, Quarter).

Here is an example of how I have my Query set-up in design view for one particular parameter (OE).



With this set-up, when I open my Crosstab query, I am prompted to input a parameter and the query runs smoothly.

However, now if I go back to my particular form, nothing gets displayed. As you can see.




I would think that you should be able to now select your particular parameters (or at least be prompted for one, of which i am not) and then the report should open accordingly when I refresh and make visible. But nothing shows up, the report itself dosen't show up.

Any thoughts?

[pbaldy]

Well, with nothing in the combo I would expect an empty report. Have you tried entering an appropriate value in the combo? Presumably you requery the report either behind the button or in the after update event of the combo(s).

[McArthurGDM]

Even if I put in a parameter, nothing becomes visible. The entire report isnt visible. I can't even see a header or anything.

[pbaldy]

Do you requery the report after entering the parameter? Is the report in Report view?

[McArthurGDM]

I "Refresh All" (sendkeys F9) with the macro, this is equivalent to requery. The report is in Report View.

[pbaldy]

I don't use macros or SendKeys (can be unreliable). I would explicitly requery the report. Can you post the db here?

[McArthurGDM]

Do you have a personal e-mail, this is a work-related document and I don't want it being spread if possible.

[June7]

Don't know about Paul, but I don't distribute my e-mail in forums.

Options:

1. attach file to thread post - follow instructions at bottom of my post - the attachment can later be deleted

2. upload file to a fileshare site such as Box.com and post link to file (the link can later be removed from post) or PM a member with the link

[pbaldy]

I'll get out of the way.

[McArthurGDM]

I tried to upload it to the site but I can't compress it below 500kb.

Do you have a dropbox?

[June7]

Reread post 8. Did you fully read the instructions at bottom of my post? 2MB zip file allowed.

[McArthurGDM]

Attachment 19164

Hopefully this works.

[McArthurGDM]

MainNavigation is the way to access the Main form with all its subforms.

StaffProjections is the particular Form with the Crosstab Query subreport.

MonthlyProjectionALL sets the stage for MonthlyProjectionALL_Crosstab.

[June7]

This is Navigation control/form. I don't like and don't use.

The MonthlyProjectionAll_Crosstab query does not have any parameters. I tried to create them and get the same outcome - blank report. Probably because of referencing Navigation form. Review https://www.accessforums.net/forms/t...orm-32053.html

There is no macro, it is VBA using SendKeys. Macros are very different thing in Access. SearchMain has SendKeys code but that has nothing to do with the projections report.


The tables in MonthlyProjectionAll are not joined. This results in a Cartesian relation of records. Is this what you want?

[McArthurGDM]

Yes, I wanted the cartesian product of all these. It would allow me to build the crosstab query I was looking for.

The fact that you got the same outcome of a blank report entails that the issue lies in the Navigation Control. I've found workarounds for almost everything within Navigation controls except this.

Ill try using a Macro instead. I used sendkeys because it typically has the same effect of requerying and reloading, etc... but ill try different ways around this.

If I figure out a solution, I'll post it.

Thanks for looking at it!