View Single Post
  #6  
Old May 31st, 2010, 01:32 AM posted to microsoft.public.access.reports
Larry Linson
external usenet poster
 
Posts: 3,112
Default PLEASE HELP: Refresh columns displayed in a report from a quer

Looks as if you have some section of your report where you have selected
"Force New Page" either Before or After.

I'm not sure what, exactly you want to see, but try "continuous forms view"
with all the fields you are displaying moved to a single line or two.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Sam" wrote in message
...
Hi Marshall, I am struggling with this for a while now:

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


Thanks in advance



"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"

--
Marsh
MVP [MS Access]
.