[resolved]access report controls from vb

guys
i am workin on a proj that needs a vb application which takes in the name of a mdb as input and it prints out the name of report,the name of the textbox present in the report and the controlsource of the textbox..it should use ms access9.0 object library..
please..any code help would be helpful..

thnx
nean

Welcome to the Forums.

I have some code on the Forums that creates the db and report/report controls.

Create Access Report From VB

HTH

hi
i dont want to create a new report..
i want to access the properties of the controls already present in a report..
i know there is an object called access.report and access.control
but how do u link that to a report in a database(mdb)..

I posted my link because it shows how to create/reference report controls.

I dont have time right now, leaving office, but when I get back I will see if I
can write a sample for you, but its basically almost the same.

Code:

---

Option Explicit
'ADD REFERENCE TO MS ACCESS XX.X OBJECT LIBRARY
Private Sub Command1_Click()

    Dim oApp As Access.Application
    Dim oReport As Access.Report
    Dim ctl As Control
   
    Set oApp = New Access.Application
    oApp.OpenCurrentDatabase "D:\RobDog888.mdb" 'Pre-Existing Database and Report.
    oApp.Visible = True
    DoCmd.Maximize
    Set oReport = oApp.Reports("MyReport")
    For Each ctl In oReport.Controls
        Debug.Print ctl.Name
    Next

End Sub

---

hi.
thanks a lot.
that code worked jus fine..
so now if i want the ctls for all the reports(say 100 in the database) and i jus need the controlsource property for the textboxes how should i modify this code?

thnx
nean

This will enumerate all reports in the database.

Code:

---

Option Explicit
'ADD REFERENCE TO MS ACCESS XX.X OBJECT LIBRARY
Private Sub Command1_Click()

    Dim oApp As Access.Application
   
    Set oApp = New Access.Application
    oApp.OpenCurrentDatabase "D:\RobDog888.mdb" 'Pre-Existing Database and Report.
    oApp.Visible = True
    DoCmd.Maximize
    For i = 0 To oApp.CurrentDb.Containers("Reports").Documents.Count - 1
        Debug.Print "Report Name: " & oApp.CurrentDb.Containers("Reports").Documents(i).Name
    Next
   
End Sub

---

hi
i tried running this code for my sample database.
but it merely opens the mdb.the names are not printed.
further i need to print all the report names,the control names of all the controls present in each report and the control source value of textboxes in a file.
how do i do this?
(ps: is "Reports" a key word or should i substitute it with a report name??)

thnx
nean

Reports is a keyword so don't replace it. It represents the collection of
reports in the referenced database. I dont have allot of time today but try working with the code. I'll be back later.

Got it. It turns out that the Reports collection does not include any reports
if they are not open. So to get around this, I display each one and output
the ControlSource then close it and move on to the next.

Code:

---

Option Explicit
'ADD REFERENCE TO MS ACCESS XX.X OBJECT LIBRARY
Private Sub Command1_Click()

    Dim oApp As Access.Application
    Dim oCtl As Control
    Dim oRpt As Report
    Dim sName As String
    Dim i As Integer
    Dim ii As Integer
   
    Set oApp = New Access.Application
    oApp.OpenCurrentDatabase "D:\RobDog888.mdb" 'Pre-Existing Database and Report.
    oApp.Visible = True
    DoCmd.Maximize
    On Error Resume Next
    For i = 0 To oApp.CurrentDb.Containers("Reports").Documents.Count - 1
        Debug.Print "Report Name: " & oApp.CurrentDb.Containers("Reports").Documents(i).Name
        sName = oApp.CurrentDb.Containers("Reports").Documents(i).Name
        DoCmd.OpenReport sName, acViewPreview, , , acWindowNormal
        Set oRpt = oApp.Reports(sName)
        For ii = 0 To oRpt.Controls.Count - 1
            If oRpt.Controls(ii).ControlType = 109 Then '109 = Testbox, add more types for the other
                                                        'controls you want that have a ControlSource
                Debug.Print "Control Name: " & oRpt.Controls(ii).Name
                If Len(oRpt.Controls(ii).ControlSource) > 0 Then
                    Debug.Print "Control Source: " & oRpt.Controls(ii).ControlSource
                Else
                    Debug.Print "Control Source: Unbound"
                End If
            End If
        Next
        DoCmd.Close acReport, sName, acSaveNo
    Next
   
End Sub

---

thnx a lot RobDog888..
the code works fine..
but i suggest that instead of opening the report in acViewNormal we can open in acViewDesign..
otherwise i have no issues..

this forum is the greatest..and the response is super..now i know where to turn to if i have any doubts..
:thumb:

thnx
nean

No prob.

I had posted it for opening the report in acViewPreview and not
acViewNormal because on my system it would print it out. acViewDesign is a
fine alternative, probably better since the report recordsource will not get
invoked (running of a query if the rs is a query).

:)