Here's the deal:
I have an unbound form with an unbound subform to perform searches based on different criteria. The form contains just a bunch of comboboxes and textboxes to choose the different criteria for what you want to search. Then after clicking a 'Search' button a SQL is built (with the appropriate tables and criteria) and set as the result subform's source object. The way I'm handling it for printing right now is basically setting the orientation to landscape and printing the form itself, which works great and since the main form's fields get printer too it helps check the criteria selected for the search, which is useful. But let's just say it's not the prettiest sight.
So, considering I don't want to have 30 different forms and reports for the different search options and I want to keep handling the search through a diynamically built SQL, I'm trying to dynamically build a fitting report if the user clicks the print button -obviously controlling beforehand that a search has been made and there's a source object for the subform. I had two approaches for that in mind.
1- Try to dynamically create a report in VBA from the results subform, same as you would by selecting it and choosing the report access button. So far I haven't been able to find a way to do that. Is it even possible through VBA?
2- Create a blank report and set the subform's source object as the report's source object, then try to make it manageable. So far it's proven as hardly doable as it sounded. Got some code from a guy trying something similar, got it working (more or less) but it's always showing me just two records per page no matter how I arrange the fields (one per line or all in a single line), and I fail to understand how it's setting each record's height (just the textbox and label's heights). So if any of you can help me tweak the code so it shows as many records per page as there's space for, I'd really appreciate it.
Code:
Function CrearInforme(strSQL As String)
Dim db As DAO.Database ' database object
Dim rs As DAO.Recordset ' recordset object
Dim fld As DAO.Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report
'set the title
title = "Resultados de búsqueda"
' initialise position variables
lngLeft = 0
lngTop = 0
'Create the report
Set rpt = CreateReport
' set properties of the Report
With rpt
.Width = 8500
.RecordSource = strSQL
.Caption = title
End With
' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
' 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 + 1500, lngTop)
txtNew.SizeToFit
' Create new label control and size to fit data.
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
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
' Open new report.
rpt.DefaultView = 0
rpt.PopUp = True
rpt.Modal = True
DoCmd.OpenReport rpt.Name, acViewPreview
'reset all objects
rs.Close
Set rs = Nothing
Set rpt = Nothing
Set db = Nothing
End Function
Which results in:
So, any way I can be using/removing those spaces marked in orange before wasting time trying to make the report look better? Thank you so much!