Hi nuber2
I had a little fun trying to solve that, because it's not a bad idea to let the user customize the report that way. I used rbowes's idea to start from. Here are the steps to achieve the goal:
1. Create a report with the maximum number of text boxes that the user can choose to see (the number of checkboxes on your form). Don't set RecordSource property.
2. For the controlsource of the text boxes write:
1st text box: field1, 2nd field2...
3. Create a public variable:
Public ReportSource as String
You do that in a module (mudules tab).
4. For each check box on your form set the tag property
In design view of the form open the property sheet for check box and under tag property write:
[Table or Query of the form].[Corresponding field name]
For example: If you have a table as a record source for a form named Recipients and a field named Name, set the tag property [recipients].[name]
5. In the OnClick event of the button that opens the report write procedure:
Dim str As String
Dim ctl As Control
Dim chk As CheckBox
Dim i As Integer
i = 0
For Each ctl In Me
If TypeOf ctl Is CheckBox Then
Set chk = ctl
If chk = -1 Then
i = i + 1
str = str & chk.Tag & " as field" & i & ","
End If
Set chk = Nothing
End If
Next
Dim j As Integer
For j = (i + 1) To x 'x = number of chkboxes
str = str & "Null as field" & j & ","
Next
'Chop off the trailing comma
str = Left(str, Len(str) - 1)
str = "select " & str & " from Table or query for the form;"
ReportSource = str
DoCmd.OpenReport "ReportName", acViewPreview
6. In the OnOpen event of the report write:
Me.RecordSource = ReportSource
That's it. It will work like this: The first checked field will be issigned to field1, 2nd to field2... To non-checked fields will be assigned value null. The sql for the report will look something like this:
select [recipients].[name] as field1,[recipients].[adress] as field2,null as field3,null as field4 from recipients;
So the first text box in report will display the first checked field, the 2nd will display 2nd checked field..., non-checked fields will display null (nothing).
Hope it will work for you. Good luck.
Mangro