subform SourceObject, MakeReportSQL -- useful for Crosstabs
---
Hi Matt,
to replace a subform recordsource while the mainform is open, do this:
1. make the subform control invisible
2. replace the subform sourceobject with a "dummy form" (make a form
with a label on it that says 'dummy' -- it will never show)
3. replace the SQL or query that the subform is based on
4. set the subform sourceobject back to the real subform
5. make the subform control visible
here is an analogy using reports -- if you have trouble adapting the
ideas for a form, we can help
you can use a report as the sourceobject of a subform -- you cannot edit
the results of a crosstab anyway
~~~~~~~~~~~
Change the RecordSource of a report before it runs
base your report on a query that is based on your crosstab. Build the
SQL for the report recordsource on the OPEN event of the report.
name your controls (and labels) in the report:
C03, c04, ...
L03, L04, ...
where 03 would be the first field for the crosstab in the query
~~~
loop through the fieldnames of your crosstab query in code and assign
the contents of the column and the label for the column
number of fields in query:
CurrentDb.QueryDefs("Queryname").Fields.Count
first fieldname:
CurrentDb.QueryDefs("Queryname").Fields(0).Name
last fieldname:
CurrentDb.QueryDefs("Queryname").Fields(CurrentDb.QueryDefs("Queryname").Fields.Count-1).Name
indexing starts with 0, so the last field is the Count-1
If your fields have spaces or special characters, you will need to
dlimit them with square brackets
[fieldname with a space or #special (character)]
~~~
then, to construct the SQL:
~~~
Private Sub Report_Open(Cancel As Integer)
'crystal
'strive4peace2007 at yahoo dot com
'NEEDS REFERENCE TO
'Microsoft DAO Library
'set up Error Handler
On Error GoTo Proc_Err
dim mCtrlname as string _
, mLblname as string, _
, mStartControlNumber as integer
, mLastControlNumber as integer
, i as integer _
, mQueryname as string _
, strSQL as string
dim db as dao.database _
qdf as dao.querydef
mQueryname = "The_name_of_your_crosstab_query"
strSQL = "SELECT firstfield, secondfield "
mStartControlNumber = 6 'wherever you want to start
mLastControlNumber = 15 'wherever you want to end
set db = currentdb
set qdf = db.QueryDefs("Queryname")
with qdf
for i = mStartControlNumber to
mCtrlname = "C" & format(i,"00")
mLblname = "L" & format(i,"00")
if i < .Fields.Count
strSQL = strSQL & ", [" & .Fields(1).name & "]" _
& " AS " & mCtrlname _
& ", '" & .Fields(1).name & "'" _
& " AS " & mLblname
else
strSQL = strSQL & ", ''" _
& " AS " & mCtrlname _
& ", ''" _
& " AS " & mLblname
end if
next i
end with
strSQL = strSQL _
& " FROM [" & mQueryname & "]"
& ";"
'-------------------------------- CHOOSE ONE
'replace SQL for report
MakeQuery strSQL, "ReportRecordSourceQuery"
'OR ---
'you can just replace the record recordsource directly
'me.Rowsource = strSQL
'--------------------------------
Proc_Exit:
On Error Resume Next
'close and release object variables
set qdf = nothing
set db = nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& me.name & " Report_Open"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End function
(NOT TESTED -- expect some debugging <smile>)
if you have criteria, add a WHERE clause before the terminating semi-colon
'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)
'modified 6-29-06
'crystal
'strive4peace2007 at yahoo dot com
On Error GoTo Proc_Err
'if query already exists, update the SQL
'if not, create the query
If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If
Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub
Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"
'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume
Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
