Get information about lines in VBA

[vtj]

I have a report in Access that has quite a number of lines(130) as print
elements. I would like to retrive two of the properties of each line and put
them in a table or even just print them to the immediate window. If I use
DAO can I write a VBA module to get the information? I would define the db
as DAO and set it to the CurrentDb. But then what is the recordset? And how
do I set it? The command would be db1.something I think. Then what is the
search criteria? Would the recordset be a SQL string of some sort? I am
trying to line up the beginning points(left) and width of many lines in an
accounting report. I could then pick a value and enter them to each line or
possible do a set command with the line.left and line.width methods. As you
can see, I am not very knowledgeable in VBA and would appreciate any info
especially a pointer to get me started or to where I can find more
information about doing this kind of thing.

 

[Douglas J. Steele]

The details of the lines in a report won't be obtainable using a recordset.

Open the report in design view, then loop through all of the controls on the
report.

Sub LineDetails()
Dim rptCurr As Access.Report
Dim ctlCurr As Access.Control
Dim strReportName As String

strReportName = "MyReport"
DoCmd.OpenReport strReportName, acViewDesign
Set rptCurr = Reports(strReportName)
For Each ctlCurr In rptCurr.Controls
If TypeOf ctlCurr Is Line Then
Debug.Print ctlCurr.Name & ", Left = " & ctlCurr.Left & ", Top =
" & ctlCurr.Top
End If
Next ctlCurr
Set rptCurr = Nothing
DoCmd.Close acReport, strReportName

End Sub

 

[Eric Gerds]

I was late,
create a new module
replace "Report1" with desired report name


Option Compare Database
Option Explicit
Private Sub bla()
Call Debug_Print_Report_Line_Properties("Report1")
End Sub
Private Sub Debug_Print_Report_Line_Properties(str_Report_Name As String)
Dim rpt As Report, ln As Line
DoCmd.OpenReport str_Report_Name, acViewDesign
On Error Resume Next 'Run-time error'13': Type mismatch
For Each rpt In Reports
If rpt.Name = str_Report_Name Then
For Each ln In rpt
With ln
Debug.Print .Name; .Left; .Width
End With
Next 'Run-time error'13' happens here when ln=nothing
End If
Next
Set ln = Nothing
Set rpt = Nothing
DoCmd.Close acReport, str_Report_Name, acSaveNo
MsgBox "press ctrl-G to see results"
End Sub

 

[vtj]

Thank you very much! Works like a charm.

The details of the lines in a report won't be obtainable using a recordset.

Open the report in design view, then loop through all of the controls on the
report.

Sub LineDetails()
Dim rptCurr As Access.Report
Dim ctlCurr As Access.Control
Dim strReportName As String

strReportName = "MyReport"
DoCmd.OpenReport strReportName, acViewDesign
Set rptCurr = Reports(strReportName)
For Each ctlCurr In rptCurr.Controls
If TypeOf ctlCurr Is Line Then
Debug.Print ctlCurr.Name & ", Left = " & ctlCurr.Left & ", Top =
" & ctlCurr.Top
End If
Next ctlCurr
Set rptCurr = Nothing
DoCmd.Close acReport, strReportName

End Sub

 

[vtj]

Thanks for your help - could not get it to run - type mismatch on For Each ln
In rpt
.. But I learn from all.