update crosstab querydef

[mharness]

Hello All,

In order to display a datasheet view of a crosstab in a subform that is
dependent on a value in the parent I have so far been able to create the
crosstab query dynamically in vba (see code below) and write it to the
querydefs collection for each parent so that it only contains the dependent
data.

I'm doing this because there does not appear to be a method for linking
parent to child as I'm accustomed to with a common subform. Please, correct
me if I'm wrong here.

Anyway, as cumbersome as it sounds it works except I have to open and close
the parent form for each parent value in order to get the new crosstab query
results. I'm looking for a better way to get the current query results in
the subform without opening and closing the parent form and expect that I'll
be in even more touble when it comes time to report this same data because I
can't be opening and closing each of the resulting 40 or so individual
reports.

Any suggestions as to how I can refresh the querydefs would be fine and I'm
completely open to another solution all together too.

Many thanks

Mike

Sub MakeADIAffidavitsXTab(ADIID As Long, strBand As String)
Dim dbs As Database
Set dbs = CurrentDb
Dim qry As String
Dim qdf As QueryDef
qry = "TRANSFORM First(qselADIAffidavits.Spots) AS FirstOfSpots " _
& "SELECT qselADIAffidavits.GM, qselADIAffidavits.CallLetters,
qselADIAffidavits.[Band], Sum(qselADIAffidavits.Spots) AS SumSpots " _
& "FROM qselADIAffidavits " _
& "WHERE (((qselADIAffidavits.[Band]) = '" & strBand & "') And
((qselADIAffidavits.ADIID) = " & ADIID & ")) " _
& "GROUP BY qselADIAffidavits.ADIID, qselADIAffidavits.GM,
qselADIAffidavits.CallLetters, qselADIAffidavits.[Band],
qselADIAffidavits.[Band] " _
& "PIVOT qselADIAffidavits.Campaign"
dbs.QueryDefs.Delete ("qtabADIAffidavits")
Set qdf = dbs.CreateQueryDef("qtabADIAffidavits", qry)
dbs.QueryDefs.Refresh
dbs.Close
Set dbs = Nothing
End Sub

 

[Wolfgang Kais]

Hello "mharness".

In order to display a datasheet view of a crosstab in a subform that is
dependent on a value in the parent I have so far been able to create the
crosstab query dynamically in vba (see code below) and write it to the
querydefs collection for each parent so that it only contains the
dependent data.

You better use the LinkChildFields and LinkMasterFirlds properties of the
subform.

I'm doing this because there does not appear to be a method for linking
parent to child as I'm accustomed to with a common subform. Please,
correct me if I'm wrong here.

That's because the ADIID column doesn't apper as a line heading.

Anyway, as cumbersome as it sounds it works except I have to open and
close the parent form for each parent value in order to get the new
crosstab query results. I'm looking for a better way to get the current
query results in the subform without opening and closing the parent form
and expect that I'll be in even more touble when it comes time to report
this same data because I can't be opening and closing each of the
resulting 40 or so individual reports.

Try the following SQL statement for the qtabADIAffidavits query
:
TRANSFORM First(qselADIAffidavits.Spots) AS FirstOfSpots
SELECT qselADIAffidavits.ADIID, qselADIAffidavits.[Band],
qselADIAffidavits.GM, qselADIAffidavits.CallLetters,
Sum(qselADIAffidavits.Spots) AS SumSpots
FROM qselADIAffidavits
GROUP BY qselADIAffidavits.ADIID, qselADIAffidavits.[Band],
qselADIAffidavits.GM, qselADIAffidavits.CallLetters
PIVOT qselADIAffidavits.Campaign;

Use the two fields ADIID and Band in both the LinkChildFields and
LinkMasterFields properties of the subform control.