records per page in detail section 1

[SPC03]

Hi,
I am creating a report at runtime, whose recordsource is SQL query. I can get a report all right, but in print preview mode it displays only three records per page in detail section. How do i print more records per page?
I have code like this.

strSqlQuery = "Select * from DishScores"
Set db = CurrentDb
lngLeft = 0
lngTop = 0
left = 0
DoCmd.OpenReport "Report1", acViewDesign

Set rpt = Reports("Report1&quot

With rpt

.RecordSource = strSqlQuery
.Caption = "TestTable Report"
.MenuBar = ""
End With
Set rstSource = db.OpenRecordset(strSqlQuery, dbOpenDynaset)
For Each fldData In rstSource.Fields
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acDetail, , fldData.Name, lngLeft, lngTop, 1200, 700)
txtNew.TextAlign = 1
lngLeft = lngLeft + txtNew.Width + 25

Next
lngTop = lngTop + txtNew.Height+25

rstSource.Close
Set rstSource = Nothing


DoCmd.OpenReport rpt.Name, acViewPreview

 

[beetee]

I've never programatically created a report.

However, the properties you can use correlate with the settings you would use in a graphically-designed report, so here are some areas to explore:

1) Set the height of the detail section.

2) Make the controls very short, and set the CanGrow properties of the detail section and controls to 'y'.

3) Play with the page settings (e.g. margins)

4) maybe you can have more success using landscape mode.

5) create a sample report *just* the way you want it using the design tool, then, using VB, iterate and view the properties of the report, and its controls and sections, and try to emulate those settings.

 

[BillPower]

Sounds like your Headers/Footers might be taking up a lot of space, the shorter Headers/Footers are the more Records you can Display in your Report, you can set their properties as in the example below:

With rpt
.RecordSource = strSqlQuery
.Caption = "TestTable Report"
.MenuBar = ""
.Section(acPageHeader).Height = 1000
.Section(acDetail).Height = 700
.Section(acPageFooter).Height = 250
End With

Look at the Section property in Help for more Constants and info.

Also, as this is an existing Report, have you at some point inadvertanly set Sorting and Grouping.

Let me know if this helps or not.

 

[SPC03]

Thank You Billpower, that worked, may i ask you one more question? There is lot of space between page header and Detail section in PrintPreview mode. How do i reduce that?
Thanks again.
SPC03