Dynamically Generating Forms and Controls

[Guest]

I am trying to report summarised financial numbers for each month......

I would like to use a Datasheet Form as a subform. The Datasheet Form needs
to be generated by the main form. The intention is to dynamically generate
SQL similar to that below and view it throgh the main datasheet. Note the
column names would change each month...hence the need to generate the subform
dyamically. Alternatively, is there a better way to do this????

SELECT D.domainName as Domain,
dbo.GetPerformanceLevel(MD.RecordId) as Level,
SUM (CASE WHEN MD.measureDate = '2005-10-01' THEN MD.ServiceCredit ELSE 0
END) as [Oct-05],
SUM (CASE WHEN MD.measureDate = '2005-11-01' THEN MD.ServiceCredit ELSE 0
END) as [Nov-05],
SUM (CASE WHEN MD.measureDate = '2005-12-01' THEN MD.ServiceCredit ELSE 0
END) as [Dec-05],
SUM (CASE WHEN MD.measureDate = '2006-01-01' THEN MD.ServiceCredit ELSE 0
END) as [Jan-06],
SUM (CASE WHEN MD.measureDate = '2006-02-01' THEN MD.ServiceCredit ELSE 0
END) as [Feb-06],
SUM (CASE WHEN MD.measureDate = '2006-03-01' THEN MD.ServiceCredit ELSE 0
END) as [Mar-06],
SUM (CASE WHEN MD.measureDate = '2006-04-01' THEN MD.ServiceCredit ELSE 0
END) as [Apr-06]
FROM dbo.MeasureDetails MD INNER JOIN
dbo.Measures M ON MD.measureId = M.measureId
INNER JOIN
dbo.Domains D ON M.domainId = D.domainId
WHERE M.timeFrame = 'Monthly'
AND MD.measureDate BETWEEN '2005-10-01' and '2006-04-01'
and dbo.GetPerformanceLevel(MD.RecordId) NOT IN ('NA','Above Target')
GROUP BY D.domainName,dbo.GetPerformanceLevel(MD.RecordId)
ORDER BY D.domainName, dbo.GetPerformanceLevel(MD.RecordId)

 

[Duane Hookom]

Assuming you are using Access, consider using a crosstab query. There is a
sample of how to set a subform control SourceObject to a query at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Check
the DynamSubfrm.mdb.

 

[Marshall Barton]

I don't think it's any different, but I don't know how an
SQL Server query interacts with all this.

First create a query (let's say it's name is
MonthlyPerformace), it doesn't matter what the SQL is
because you are going to replace it.

In your code, assign your constructed SQL statement to the
QueryDef's SQL property:

CurrentDb.QueryDefs!MonthlyPerformace.SQL = "SELECT ..."

Then set the subform control's SourceObject property:

Me.subformcontrol.SourceObject = "Query.MonthlyPerformace"