View Single Post
  #6  
Old May 31st, 2010, 12:45 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default PLEASE HELP: Refresh columns displayed in a report from a quer

You have put a lot of effort (and learned some significant
things) that, unfortunately, should NOT be used for what you
are trying to accomplish. The CreateReport and
CreateReportControl are intended for programmers to create
their own DESIGN time wizards. You may have thought that's
a capability you want to provide to your users, BUT users
are operating at run time, not design time, so CreateReport
and CreateReportControl are best left on the sidelines.

Instead of that, you should create a basic report with
enough text boxes for the fields users might need. Name the
text boxes as usual for the fields that will always be there
(eg. ID, lastname, etc) and the text boxes that will display
user selected fields tctSelect1, txtSelect2, ...

Then you can use code like I posted earlier to bind the
optional text boxes to the user selected fields in your
constructed SQL statement.

A very important point is that all the code that sets
properties in the report (RecordSource, ControlSource, etc)
needs to be in the report's Open event procedure.

If you have difficulty wrapping your head around this
approach, please try to ask specific questions about
individual aspects. Otherwise we will be going back and
forth while I try to guess what you are struggling with and
replying with general lectures about dynamic reports.
--
Marsh
MVP [MS Access]


Sam wrote:
My issue: I want to generate dynamic reports based on dynamic sql queries,
where "Select" and "Where" Clause changes with user selection on a user form
in access:

I am able to generate the dynamic reports now, BUT the issue I am having now
is that the reports are displayed in separate pages and, I want to display
them in a tabular format. Can you PLEASE help me with this?
The results are display like this, all on separate pages:

ID: 1
Name: tom
City: New York
Age: 26

ID: 2
Name: Jim
City: New York
Age: 28

ID: 3
Name: Chris
City: New York
Age: 32

I want to display the results like this:
ID Name City Age
1 Tom New York 26
2 Jim New York 28
3 Chris New York 32

Here is my code so far:
Private Sub GenerateReport_Click()

Dim rpt As Report, rs As Recordset, db As Object, fld As Object, stSql As
Object, lngTop As Long, lngLeft As Long
Dim txtNew As Object, lblNew As Object

'Create the report
Set rpt = CreateReport
rpt.RecordSource = "Report_Query"

' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Report_Query")

'Create Label Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Title", 0, 0)
lblNew.FontBold = True
lblNew.FontSize = 12
lblNew.SizeToFit

' Create corresponding label and text box controls for each field.
For Each fld In rs.Fields

' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail _
, , fld.Name, lngLeft + 2500, lngTop)
txtNew.SizeToFit

' Create new label control and size to fit data.
Set lblNew = CreateReportControl(rpt.Name, acLabel, acHeader, _
txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit

' Increment top value for next control
lngTop = lngTop + txtNew.Height + 25
Next

' Create datestamp in Footer
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageFooter, , Now(), 0, 0)

' Create page numbering on footer
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width -
1000, 0)
txtNew.SizeToFit

DoCmd.OpenReport rpt.Name, acViewPreview


"Marshall Barton" wrote:
sam wrote:
In the following code you gave:

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

Me.txtSelect1.ControlSource is refering to the column header in the report?


Me.txtSelect1 is the name of the report text box that you
want to bind to the field a user specified in the form
text/combo box, also named txtSelect1. The ControlSource
property is where you need to put the name of the field with
the values yo want the text box to display.

Since I have no idea what kind of headers you are using nor
what you want them to display, I did not try to comment on
that.

and Forms!theform.txtSelect1 is the field from the form?


Yes, that is the form text box where users specify the name
of a field that you put in the report's record source query.

If I have the right understanding, once I do the above code: then I open the
report with: DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 &
strWhere2


That may or may not be correct depending on what you are
putting in the strWhere strings. The commas look to be
wrong to me. The end result should look something like:

DoCmd.OpenReport "Student Report", acViewPreview, ,
strWhereAll

with strWhereAll containing something along these lines:
thisnumberfield=123 And thattextfield="ABC"