Cross Tab Query as a subform (1 Viewer)

[RCheesley]

Hi Guys,

I've searched around for this but I can't find an answer!

Spent ages trying to figure out how to display this dataset, then figuring out cross tab queries, parameters for cross tab queries etc.

Finally got it working (a biig *WOOHOO* moment!) and thought the next stage would be easy peasy .. hahaha.

I have created a form based on a crosstab query + parameter, and it is working. This parameter is taking a value from the main form to filter the query, and if I load the form by itself then it works perfectly.

As soon as I embed the form as a subform I get the dreaded message "you fool, you can't do that!".

Is there any way of getting around this? I saw someone mention something about temporary tables but I'm not sure how to do this.

Basically my database is a stock management system, and I have an ordering form whereby the stock is ordered, and below it is a subform which allows ordering of 3 consumable items to go with that order.

The crosstab query is going to be used as another subform to identify what quantities have already been ordered of the three consumable items by the location ordering (this is what the parameter does - it restricts the crosstab query to the LocationID on the main ordering form).

Ultimately I want to do some kind of whizzyness to calculate the difference between the total number of consumables ordered and the total required based on the number of products which gets a wee bit complex (consumable 1 requires 1 per product, consumables 2 and 3 require 1 per 10 products).

*confoozled*!

Ruth

 

[DCrake]

Basically a sub form is loaded into memory before the main form is, and that is the root of your problem as the source data is reliant on the main form data.

What you could do is to create you main form sub layout as normal but don't set the source object of the subform. When you select the criteria from your main form that is passed to underlying query of the subform set the source object then.

So when you main form opens you subform will be blank, then as soon as you make your selection from the main form it will pass the value to the subform query and load the subform with the populated data form the crosstab.

David

 

[Dranoweb]

I know this is dredging up an old topic, and I am aware some people find this irritating (Not sure why though)

I have a similar issue, and if i understand correctly;

Are you suggesting that one should call the subform to be loaded on an event from the mainform (such as a control) or during the "on load" event of the mainform?

 

[Dranoweb]

Just figured out my problem - for anyone that finds this trough google like I did.

Make a SIMPLE QUERY, using the wizard in access 2007, and choose the summary option.

this will generate counted totals similar to a crosstab that can be used as a subform/subquery without the need to define column headings.