RE: Creating User Customize Form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

.

Quantcast