While you can create an entire report on-the-fly using VBA, it might be
easier to place hidden controls for all the possible fields in your report,
and pass the names of the selected fields in the optional OpenArgs parameter
of the OpenReport method. You can set the Visible and Left properties in the
report's On Open event.
' The code assumes that the textbox corresponding to each checkbox has the
same
' name except a different prefix, e.g., chkName/txtName. While it has basic
' error-handling, you may wish to augment it (for example, as it's currently
' written, it will cause an "Invalid Use of Null" error if the report is
opened in any
' context than by the control button and an "Invalid Procedure Call or
argument"
' error if no fields are selected).
' Command button On Click event code
On Error GoTo Err_Handler
Dim strOA As String ' OpenArgs string
Dim stDocName As String ' Report name
Dim ctl As Control ' Control variable
strOA = ""
' Loop through all controls
' If control name matches array element, add to OpenArgs string
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value = True Then
strOA = strOA & "txt" & Mid(ctl.Name, 4) & ","
End If
End If
Next ctl
' Strip off last comma
strOA = Left(strOA, Len(strOA) - 1)
' Change to the name of your report
stDocName = "Report1"
' Open report in preview mode with optional OpenArgs parameter
DoCmd.OpenReport stDocName, acPreview, , , , strOA
Err_Exit:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Err_Exit
' Report On Open event code
On Error GoTo Err_Handler
Dim astrControl() As String ' Control name array
Dim i As Integer ' Loop counter
Dim ctl As Control ' Control variable
Dim sglCurPos As Single ' Position to place next visible control
' Horizonal offset, in twips, from one control to the next
' 1440 twips = 1 inch, set here to .05"
Const cintOffset As Integer = 72
sglCurPos = 0
' Parse OpenArgs to string array
astrControl() = Split(Me.OpenArgs, ",")
For i = 0 To UBound(astrControl)
For Each ctl In Me.Controls
If ctl.Name = astrControl(i) Then
ctl.Visible = True
' Set left edge of control to sglCurPos
ctl.Left = sglCurPos
' Increment sglCurPos
sglCurPos = sglCurPos + ctl.Width + cintOffset
Exit For
End If
Next ctl
Next i
Err_Exit:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Err_Exit
End Sub
Hope that helps.
Sprinks
Note that you must also ensure that the total width of the selected fields
and corresponding offsets does not exceed the page width.
Sprinks
missinglinq - 23 May 2007 22:11 GMT
You might want to simply invoke the Report Wizard behind a button, for
instance:
Application.Run "acwzmain.frui_Entry", "ReportSourceGoesHere", acReport
Run as given above, the Wizard will open up and give the user the option of
picking the source for the report. You could also set up a hack to enter the
name of the table/query to use for the report, maybe a dropdown box to do
this, hen substitue the actual source name for "ReportSourceGoesHere". This
would have the Wizard open with that table/query already selected.

Signature
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000
Please Help - 24 May 2007 14:18 GMT
Good morning guys,
Thanks for your helps!
> You might want to simply invoke the Report Wizard behind a button, for
> instance:
[quoted text clipped - 6 lines]
> this, hen substitue the actual source name for "ReportSourceGoesHere". This
> would have the Wizard open with that table/query already selected.