Hi Dave
"Michael (michka) Kaplan [MS]" <mi*****@online.microsoft.com> wrote in
message news:40********@news.microsoft.com...
You can use CreateReport and CreateReportControl, the programmatic
commands that create a report (see online help for details).
I have recently helped (I hope) someone using the CreateReport method that
Micheal has mentioned above.
Here is the code (along with a little background info) that I / we ended up
using, if it might be of some help to you. :
We were wanting to create a report that displayed 2 "static" fields
("FirstName" and "LastName"), as well as a variable number of checkbox
fields that contained "True" values (IOW - display checkboxes that had been
"checked")
The basic premise is to build an SQL string ("MySQL") which is filtered by
the items selected in a multi-select listbox.
This SQL is used for the Recordsource of a subform, which is intended as a
"preview" of what you will see on the report that is about to be created.
This same SQL string is then used as the Recordsource of the report ... so
what you see in the subform is what you get in the report!
Just say "NO" when prompted to save the report after viewing or printing it.
(It's not nessecary to save as we want to re-create it each time.)
************************************************** **
Option Compare Database
Option Explicit
Dim MySQL As String 'This is declared in the form's declaration section so
that it can be used in more than one procedure on this form.
'-------------------------------------------------------------------------
Private Sub cmdCreateReport_Click()
'FYI notes that apply to the code below
'------------------------------------------
'Section(0) - Detail Section
'Section(1) - Report Header
'Section(2) - Report Footer
'Section(3) - Page Header
'Section(4) - Page Footer
'Section(5) - Group Level 1 Header
'Section(6) - Group Level 1 Footer
'Section(7) - Group Level 2 Header
'(all sizes in twips)... 1 twip = 1/1440 of an inch
'CreateReportControl(reportname, controltype[, section[, parent[,
columnname[, left[, top[, width[, height]]]]]]])
------------------------------------------
sRequerySubform ' Makes sure that the data is up-to-date as of the time that
this button is pressed.
Dim rpt As Report
Dim MyCtl As Control 'Probably could have used this for various control
types.
Dim ctlTextBox As Control
Dim ctlListBox As Control
Dim ctlLabel As Control
Dim ctlCheckBox As Control
Dim varItm As Variant
Dim strField As String
Dim intDataX As Integer, intDataY As Integer
Dim intLabelX As Integer, intLabelY As Integer
Set ctlListBox = Me.lstFieldList
' Create new report with tblCustomers as its record source.
Set rpt = CreateReport
rpt.RecordSource = MySQL 'Yup same good old SQL string
rpt.Section(0).Height = 500
rpt.Section(4).Height = 300
rpt.Width = 9360 '6.5 x 1440 twips/inch
'Set up the page header section (which is pretty "Plain Vanilla")
'------------------------------------------------------------------
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acPageHeader, , , 100,
100)
ctlLabel.Caption = "Whatever you want to call this report."
ctlLabel.Height = 720
ctlLabel.Width = 7000
ctlLabel.FontName = "Times New Roman"
ctlLabel.FontSize = 18
Set MyCtl = CreateReportControl(rpt.Name, acLine, acPageHeader, , , 0, 840,
9360)
'----------------------- Create the "fixed" label / textbox
fields --------------------------
Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"FirstName", 1000, 100)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "FirstName", 100, 100)
Set ctlTextBox = CreateReportControl(rpt.Name, acTextBox, acDetail, ,
"LastName", 1000, 400)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, acDetail,
ctlTextBox.Name, "LastName", 100, 400)
'----------------------- Create the "variable" label / checkbox
fields --------------------------
' Initialize positioning values for the first pair of variable controls.
intLabelX = 2880
intLabelY = 100
intDataX = 3880
intDataY = 100
If ctlListBox.ItemsSelected.Count > 0 Then
For Each varItm In ctlListBox.ItemsSelected
strField = ctlListBox.ItemData(varItm)
Set ctlCheckBox = CreateReportControl(rpt.Name, acCheckBox, , ,
strField, intDataX, intDataY)
Set ctlLabel = CreateReportControl(rpt.Name, acLabel, ,
ctlCheckBox.Name, strField, intLabelX, intLabelY)
intLabelX = intLabelX + 1440 'each label is 1" apart
'intLabelY = 100
intDataX = intDataX + 1440 'each checkbox is also 1" apart
'intDataY = 100
Next varItm
End If
DoCmd.Maximize ' Maximize the report
DoCmd.RunCommand acCmdPrintPreview 'Switch to Print Preview mode
End Sub
'-------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
sRequerySubform
End Sub
'-------------------------------------------------------------------------
Private Sub lstFieldList_AfterUpdate()
sRequerySubform
End Sub
'-------------------------------------------------------------------------
Public Sub sRequerySubform()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.
'in order to alow the user to view:
' 1.) ALL records
' 2.) filtered by one field only
' 3.) filtered by multiple fields
Dim ctl As Control
Set ctl = Me.lstFieldList
Dim Msg As String
Dim CR As String
CR = vbCrLf
Dim varItm As Variant
Dim whr As String
'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT tblCustomers.*"
MySQL = MySQL & " FROM tblCustomers "
'Build the WHERE portion
whr = "" 'initialize variable
If ctl.ItemsSelected.Count > 0 Then
For Each varItm In ctl.ItemsSelected
If Len(whr) > 0 Then
whr = whr & " OR " & ctl.ItemData(varItm) & "= True"
Else
whr = whr & ctl.ItemData(varItm) & "= True"
End If
Next varItm
End If
If Len(whr) > 0 Then
MySQL = MySQL & "WHERE (" & whr & ")"
End If
MySQL = MySQL & "; "
'Debug.Print MySQL
Me.sbfCustomers.Form.RecordSource = MySQL
Set ctl = Nothing
End Sub
************************************************** **
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================