excluding columns from a report using an option box

[mstekkie]

i have a report that has to be printed out; it contains employee info, test results, occupation and so on. anyways, the user wants to be able to choose which columns they want to see on the report.

i've thought of just making different reports (based on the possible options) but there may be an option that the user wants that's not on the list.

i'd ideally like to make an option box, or check box that allows the user to check which columns they want to see in the report. is this even possible to accomplish?

thank you in advance.

cate

 

[Larsson]

Well as I see it you need to change the question depending on what the user select. One way to do this is to create the question in vb-code and then use that "string" as source object for you report.

Here is some sort of exampel of what I am talking about. It is not complete, so you will have to fill out the rest.

Function test()
Dim sqlstring As String

sqlstring = "SELECT "
For Each userCheckBox In yourForm
'Use a good name for the checkbox so that you can use it
'in the string.
sqlstring = sqlstring & "nameOfColumn, "
Next

'Remove last comma.
sqlstring = Left(sqlstring, Len(sqlstring) - 1)

'The rest of the sql expression.
sqlstring = sqlstring & "FROM Tabel WHERE criteria;"

Report.RecordSource = sqlstring
End Function


I hope this help.
Larsson

 

[mstekkie]

thank you very much, i'll try it out and let you know what happens.

cate

 

[mstekkie]

where would i put this code? and i don't know what to put for criteria.

 

[Larsson]

You need to put this code in the OnClick event for the button that opens the report.

You only use the criteria to narrow the output, for exampel, if you have field with names and you only want the name John, then the critera would be: name = "John"

If you have anymore question, feel free to ask.
Larsson

 

[mstekkie]

do i just erase all the other code that was generated for the button by the wizard?

 

[mstekkie]

whenever i press the open report button, i keep getting the error that it's expecting an 'end sub' but there's already an end sub at the end of the onclick procedure. any idea why that's happening?

 

[Larsson]

Leave the code that is generated by the wizard. It should look something like this:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String

'The new code is put here before the report is opened.

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


Post your code so I can take a look at it.

Larsson