How to link a form to a crosstab query

[ManuelLavesa]

Basically what I am trying to do is to link the crosstab query to a Form. the crosstab query has the following SQL:

TRANSFORM Sum(SubQryTotalProductSales.SumOfQtyOrdered) AS SumOfSumOfQtyOrdered
SELECT SubQryTotalProductSales.fkProductID, SubQryTotalProductSales.ProductName
FROM SubQryTotalProductSales
GROUP BY SubQryTotalProductSales.fkProductID, SubQryTotalProductSales.ProductName, SubQryTotalProductSales.YrOrder
ORDER BY SubQryTotalProductSales.YrOrder DESC
PIVOT SubQryTotalProductSales.YrOrder In ("2015","2014","2013","2012");

The Main form was made from my Product table and contains all the product related fields. ProductID, ProductName, Description, X_Ref, ListPrice, OurCost, fkSupplier, Discontinued, ReorderLevel etc....
Then I made a Subform with the crosstab query, and tried to place it in the main form. So far everything seemed to work. The next thing was to make the Child and Master Link, I used the fkProductID from Child (Subform), and the ProductID from Master. So there are no errors, but I don't get the link to work, that is when I change the product Field in the Master the Subform does not change accordingly. When I change the view to design view and then change again to Form view, it opens up with the correct product in both Master and sub. Except in the Sub the productName Shows the productID instead of product Name. the question is what am I doing wrong? Is there a solution to this? Thank you for your help.

[CJ_London]

my guess is you are using lookups in your table design - they often cause this sort of issue.

[ManuelLavesa]

Thank you for your answer. As far as I know there are no lookups in the form at all. And the table is just a normal table. I did not use the DLookup either. So it might be something related but I do not know.

[June7]

If fkProductID is in the subform and that field has lookup in the table, the alias value will not get pulled through to the crosstab query. The crosstab will show the actual value from the field.

What do you mean by 'change the product field in the master'? You are changing the value in the record?

[ManuelLavesa]

I have a combo box in the Master form which changes the ProductID and ProductName, the column for the ProductID is hidden. So when I change the productName through the combo box everything in the master form changes accordingly, but the subform does not change. Then if I go to design view and back to Form view, the fields in the subform change to match the Master, but the ProductName changes to the ProductID in the subform which has the crosstab query.

[ManuelLavesa]

Yes fkProductID is in the subform, that is how I connected the forms. So you are right that is looking to the table. Why doesn't it pull the alias value through to the crosstab query? Is there a way around this? Maybe with VBA?

[LaughingBull]

I have found that if you go to design view and click on fields available and move the name of the field you want to show up place that on the form again and make the other invisible and the correct info shows up

[ManuelLavesa]

You will have fun with this one... I just got rid of the combo box and use the Navigation buttons to search for the records and it works. All the information in the crosstab query shows properly. Does anybody know why the combo box can cause such trouble?

[June7]

Was the combobox UNBOUND or BOUND - was ControlSource property set?

[ManuelLavesa]

The combobox was bound, The control source was ProductName , The row source was set to table product ProductID, and ProductName, set the column count to 2 and column width to 0";1" . As usual for combo boxes. But in this case it did not work.

[June7]

What was purpose of combobox? If it was to select/enter value into record it would be BOUND. If it was to input filter criteria then it must be UNBOUND and code would use the input to filter form RecordSource.

[ManuelLavesa]

The purpose of the combo box was to select/enter value, so it was bound. But I will try to make it unbound and use it to filter criteria and see if this way it works... Thank you for the idea....

[ManuelLavesa]

It works!!!! The Unbound Combobox was the answer... So Thank again for all your help