I want to create custormizable report using a crosstab query.For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query and then what i did was i created a temparary table and insert the values into that temp table using the crosstab query. now what i want is to generate the report using that temp table but every time user selects different row headings and column headings then the report will be changed. so i found a way to create a report using VBA code but at the moment im stuck with dat code.... here's the code for the crosstab query and the temp table and the code for generate the report. :-
-----------------------------------------------------
(cboColumns/cboProjectTitle/cboRows/cboIndicator)
These are the combobox parameters pass to the crosstab query after button's being clikced.
------------------------------------------------------------
Cross Tab Query
(sSql = "TRANSFORM Max(IndicatorData.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cboRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" + cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND Region.nRegionId=IndicatorData.nRegionId " + _
"GROUP BY IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cboColumns, "_", ".")) + ",'yyyy') "
)
-------------------------------------------------------------------------------------------------------------------
Temp Table
Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Dim recCrossTab As Recordset
Dim sString, sFields, sValues As String
sString = "CREATE TABLE X ("
Set recCrossTab = dbCurr.OpenRecordset(CrossTabSql)
For i = 0 To recCrossTab.Fields.Count - 1
If i = recCrossTab.Fields.Count - 1 Then
sString = sString + recCrossTab.Fields(i).Name + " Text(50) "
sFields = sFields + recCrossTab.Fields(i).Name
Else
sString = sString + recCrossTab.Fields(i).Name + " Text(50), "
sFields = sFields + recCrossTab.Fields(i).Name + ", "
End If
Next
sString = sString + ") "
Debug.Print sString
DoCmd.RunSQL "Drop Table X"
DoCmd.RunSQL sString
Dim InsertStr As String
InsertStr = "INSERT INTO X (" + sFields + ") values ("
''InsertStr = Replace(InsertStr,"'",""")
recCrossTab.MoveFirst
While (recCrossTab.EOF = False And recCrossTab.BOF = False)
For b = 0 To recCrossTab.Fields.Count - 1
If b = recCrossTab.Fields.Count - 1 Then
sValues = sValues + "'" + CStr(recCrossTab.Fields(recCrossTab.Fields(b).Name )) + "' "
Else
sValues = sValues + "'" + CStr(recCrossTab.Fields(recCrossTab.Fields(b).Name )) + "', "
End If
Next
InsertStr = "INSERT INTO X (" + sFields + ") values (" + sValues + ") "
Debug.Print InsertStr
DoCmd.SetWarnings False
DoCmd.RunSQL InsertStr
InsertStr = "INSERT INTO X (" + sFields + ") values ("
sValues = ""
recCrossTab.MoveNext
Wend
-------------------------------------------------------------------------------------------------------------------
Creating the Report using the VBA Code. This Code is in the Code View of the Report called Custom Report. (Code is taken from:- http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch15.mspx)
Private Property Let CustomReport_Source(RHS As String)
' Create report based on specified data source.
Dim txtNew As Access.TextBox
Dim lblNew As Access.Label
Dim rstSource As ADODB.Recordset
Dim fldData As ADODB.Field
Dim lngTop As Long
Dim lngLeft As Long
lngLeft = 0
lngTop = 0
' Set report's RecordSource property.
Me.Report.RecordSource = RHS
' Open recordset on specified record source.
Set rstSource = New ADODB.Recordset
rstSource.Open "SELECT * FROM [" & RHS & "];", _
CurrentProject.Connection, adOpenForwardOnly
' Create corresponding label and text box controls for each field.
For Each fldData In rstSource.Fields
' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(Me.Report.Name, acTextBox, _
acDetail, , fldData.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit
' Create new label control and size to fit data.
Set lblNew = CreateReportControl(Me.Report.Name, acLabel, acDetail, _
txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
' Increment top value for next control.
lngTop = lngTop + txtNew.Height + 25
Next
CustomReport_Source_End:
On Error Resume Next
rstSource.Close
Set rstSource = Nothing
Exit Property
End Property
-------------------------------------------------------------------------------------------------------------------
In here we can send the created temp table but I cant access the above property. so i created a sub-routine. and call that in the following Sub Report_Open() method. But It gives an error like this "U must be in design view to create or delete Controls".
Private Sub Report_Open(Cancel As Integer)
CustomReport_Source("X")
End Sub
pls someone help me out or please tell me is there any other way to do this kind of requirement.
11
