Creating User Customize Form

[Please Help]

Good morning,

I would like to create a form for a report where users can select the fields
on the form that they want on the report.

For example, on the left side of the form, we have a list of fields, and the
users can move the fields that they want from the left side of the form to
the right side. Then click on a command button to preview the report.

I went through my Access 2003 book, and I do not see a way doing that.

Can someone help me or point me to the sources on doing?

Thanks.

 

[Sprinks]

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

 

[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 via AccessMonster.com]

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.

 

[Please Help]

Good morning guys,

Thanks for your helps!