Cross tab Query and a dynamic subForm

[molly]

I have a cross tab qry shown in datasheet view on a subform with no problem. It shows years as rows and three codes across for a price type. Later, If I add a new price type code, which will become a column in the cross tab, I will need to edit the subform for this fourth column that shows on the subform.

Is there a way for the subform to dynamically or automatically pick up a new cross tab column?

I worry that I will forget to edit the subform.

Molly

 

[neemi]

the cross tab columns should automatically be picked up as long as you have not defined any in the queries Column property. If you leave this property blank whenever you add another price type code then the cross tab will display the results.
Hope this helps.
neemi

 

[molly]

hI. I think that I have a real oddball situation. Or maybe something that Access cannot do.

Attached is a mini test file if you all have time to peek at. My goal is to make a cross tab query to work on a subform within the Main form.

The subform 8162sfrmCrosstab works fine when run by itself.
Except a second flaw that i will mention later.

But when I run the 8002frmJunction, the subform inside boms out. I get an error message:

"You can’t use a pass-through query or a non-fixed –column crosstab query as a record source for a subform. Before you bind the subform to a crosstab query, set the query’s column headings properly."

I click OK and I get my screen but the subform is always blank. Is there a fix?

The 2nd issue is when I add a new pricelevel code in tblData, the 8162Qry picks it up just fine, but the 8162sfrmCrosstab does not. It expects me to make a form field for anything new. Anyway to make the form automatically pick up on a new code just like the Qry did?

So I have 2 dooooozzzzies. Maybe I pushed Access to far or I am pushing my brain too far.

File attached.

Regards
Molly

 

[molly]

I did more internet research on this problem. I see that this is a major Access flaw that someone works around with the Transform command. I have to study this. Here are 2 old websites that speak to my same problem. FYI.

2007:

http://putxi.wordpress.com/2007/03/16/segona-pagina/

2005:

http://ewbi.blogs.com/develops/2005/01/access_sql_tran.html

Maybe these ideas can be applied to my simple model sent earlier. I see that this is not an easy request. Molly