Form to Allow Users to Select Fields to Appear on Report

  • Thread starter Thread starter Uninvisible
  • Start date Start date
U

Uninvisible

I have created form which allows users to click a command button to
generate a report of contacts. Users have an option of selecting a
check box (Check01) to include all fields in the report, which is then
referenced in the report's open event:

lngPos = 0.25 * 1440 'start 1/4 inch from left edge
With Forms!FRM_TEST
If .Check01 = True Then
Me.F_NAME.Visible = True
Me.F_NAME.Left = lngPos
lngPos = lngPos + Me.F_NAME.Width
Me.L_NAME.Visible = True
Me.L_NAME.Left = lngPos
lngPos = lngPos + Me.L_NAME.Width
Me.ADDRESS.Visible = True
Me.ADDRESS.Left = lngPos
lngPos = lngPos + Me.L_NAME.Width
Me.PHONE.Visible = True
Me.PHONE.Left = lngPos
lngPos = lngPos + Me.L_NAME.Width
Me.EMAIL.Visible = True
Me.EMAIL.Left = lngPos
lngPos = lngPos + Me.L_NAME.Width

End If

If users do not select the checkbox, an unbound list box (LST_FIELDS)
is enabled and allows users to multi-select the field names (two
columns: 1; F_NAME;2; L_NAME;3;ADDRESS;4;PHONE;5;EMAIL) they wish to
populate the report. Where I am stuck is in referencing the
selections from the list box as part of an ELSE condition on the
report's open event so it populates the report with data only from the
fields selected. Any thoughts?
 
You will need to loop through the ItemsSelected collection of the list box,
setting the Visible property for those selected.

This kind of thing:

Dim varItem As Variant
Dim strFieldName As String
With Forms!FRM_TEST.LST_FIELDS
For Each varItem In .ItemsSelected
strFieldName = .ItemData(varItem)
Me(strFieldName).Visible = True
Next
End With

Before you do that, you probably want to set Visible to No for all the
relevant controls so the unselected ones hide.
 
Back
Top