Prev: Final Reminder - Microsoft Responds to the Evolution of Community
Next: Printing report with many fields
From: sam on 28 May 2010 12:49
Hi All,

I have a report whose record source is a query, the query consists of
dynamic Select and Where clause and so the columns are user driven (based on
what user selects on a form)

I am able to display the data to the report, however I am not able to
refresh the columns in the report, For eg:

My SQL is:

strSQL = Select Student_Id, Student_FName, Student_LName & strSelect1 &
strSelect2 & strSelect3 FROM Students_Table WHRE strWhere1, strWhere2,
strWhere3

Now, I am able to display the specific columns in the query based on what
users select in addition with Student_Id, Student_FName, Student_LName
columns, However I am not able to do the same with the report, I am not able
to display the additional columns that user selects in the form in the report
(strSelect1 & strSelect2 & strSelect3)

I have assigned this command to a button to generate the report:

DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & strWhere2 &
strWhere3

How can I resolve this?

Hope I made it clear.

Thanks in advance
From: Marshall Barton on 28 May 2010 15:17
sam wrote:
>I have a report whose record source is a query, the query consists of
>dynamic Select and Where clause and so the columns are user driven (based on
>what user selects on a form)
>
>I am able to display the data to the report, however I am not able to
>refresh the columns in the report, For eg:
>
>My SQL is:
>
>strSQL = Select Student_Id, Student_FName, Student_LName & strSelect1 &
>strSelect2 & strSelect3 FROM Students_Table WHRE strWhere1, strWhere2,
>strWhere3
>
>Now, I am able to display the specific columns in the query based on what
>users select in addition with Student_Id, Student_FName, Student_LName
>columns, However I am not able to do the same with the report, I am not able
>to display the additional columns that user selects in the form in the report
>(strSelect1 & strSelect2 & strSelect3)
>
>I have assigned this command to a button to generate the report:
>
>DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & strWhere2 &
>strWhere3

Assuming the strSelect# strings contain a leading comma and
the strWhere# strings conatain " AND " in all but the first
or last string, I guess you question is how to bind report
text boxes to the specifisl fields. If so, use the report's
Open event to do it:

With Forms!theform
Me.txtSelect1.ControlSource = .txtSelect1
. . .
End With

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Final Reminder - Microsoft Responds to the Evolution of Community
Next: Printing report with many fields