Crosstab query as source for form & filtering/editing

[merry_fay]

Hi,

I know there's a lot of question around on this, but I'm really struggling to understand what's going wrong.

This is my code:

PARAMETERS forms!frm_Sys_TCID_FC!txtSys Long, forms!frm_Sys_TCID_FC!cmbyr Long, forms!frm_Sys_TCID_FC!cmbtcid Long; 
 
TRANSFORM Sum(tbl_System_TCID_FC.Percent) AS SumOfPercent 
 
SELECT tbl_System_TCID_FC.TCID, tbl_System_TCID_FC.[System Code], tbl_System.[System Name], tbl_System_TCID_FC.Year, IIf(tbl_Valid_TCIDs!TCID Is Null,1,0) AS Valid 
 
FROM (tbl_System_TCID_FC LEFT JOIN tbl_Valid_TCIDs ON tbl_System_TCID_FC.TCID=tbl_Valid_TCIDs.TCID) LEFT JOIN tbl_System ON tbl_System_TCID_FC.[System Code]=tbl_System.[System Code] 
 
WHERE tbl_System_TCID_FC.[System Code]=forms!frm_Sys_TCID_FC!txtSys AND tbl_System_TCID_FC.TCID=forms!frm_Sys_TCID_FC!cmbtcid AND tbl_System_TCID_FC.Year=forms!frm_Sys_TCID_FC!cmbyr 
 
GROUP BY tbl_System_TCID_FC.TCID, tbl_System_TCID_FC.[System Code], tbl_System.[System Name], tbl_System_TCID_FC.Year, IIf(tbl_Valid_TCIDs!TCID Is Null,1,0) PIVOT tbl_System_TCID_FC.MthNo;

1st problem: When I filter on a combination that I know exists, the form shows nothing (the 3 cells in the parameter all have me.requery on their afterupdate events)

2nd problem: (might be OK once the 1st problem is fixed) Sometimes I might want to filter on only 1, none or 2 of the 3 paramaters. I've set the default value in these to *. At the moment, as above, there's nothing showing on the form though.

Next challenge -I can't check this bit until I've got the first bit working, but just in advance as I know I'm going to get stuck.... Having used a crosstab query as source for the form, I still want to be able to edit it though, BUT if I, eg changed percent from 10 to a 20 for System Code 3, TCID 1234, for May 11 (the table has 3 date control columns -Year (eg 2011), MthNo (eg 5) & MthDate (eg 01/05/2011)), I want it to update the table for all instances of that combination for May 11 & any dates greater than that. Will I have to run an update query on the after update event for each of the 12 percent cells?

Thanks