Automating report in a different MDB ( Question #2 )

[xzzy]

I need to automate a report in a different database (and thank yous to Terry
Kreft for pointing me in the right direction).

below is the code with the one line that does not work, marked: 'Does not
Work'

It appears that the type of object returned from the "AllReports" collection
is not a report object.
=>> I need it to be an object of type report.
'for automating remote report
Dim App As Access.Application
Dim myDb As Database
Dim dbs As Object
Dim RPT_Copy As Access.Report
Dim strDBPath As String
Dim obj As Object
Const REPORT_NAME_Copy = "MyReport"

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

With App
.OpenCurrentDatabase strDBPath, False
Set myDb = .CurrentDb
Set dbs = App.CurrentProject
For Each obj In dbs.AllReports
If obj.Name = REPORT_NAME_Copy Then

'Does not Work - the objects are different types
Set RPT = dbs.AllReports(REPORT_NAME_Copy)
'end of Does not Work

If RPT Is Nothing Then
OpenReport = False
On Error Resume Next
myDb.Close
Set myDb = Nothing
App.Quit acQuitSaveNone
Set App = Nothing
Exit For
Else
OpenReport = True
Exit For
End If
End If
Next obj
End With

Thank you,
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

[Danny J. Lesandrini]

I haven't used this process with Access XP, but in Access 97 you
could use a similar method to get the name of a report from the
Reports Container, as you have with the AllReports object.

The problem is, you can't set a report object to it, since it isn't
open. All you have is a name. Add this line ...

DoCmd.OpenReport "[report name]",acViewDesign,,,acHidden

Once it's opened, then the next line, where you set a report object
to it, will work.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message news:6jJBb.356152$275.1163344@attbi_s53...

I need to automate a report in a different database (and thank yous to Terry
Kreft for pointing me in the right direction).

below is the code with the one line that does not work, marked: 'Does not
Work'

It appears that the type of object returned from the "AllReports" collection
is not a report object.
=>> I need it to be an object of type report.
'for automating remote report
Dim App As Access.Application
Dim myDb As Database
Dim dbs As Object
Dim RPT_Copy As Access.Report
Dim strDBPath As String
Dim obj As Object
Const REPORT_NAME_Copy = "MyReport"

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

With App
.OpenCurrentDatabase strDBPath, False
Set myDb = .CurrentDb
Set dbs = App.CurrentProject
For Each obj In dbs.AllReports
If obj.Name = REPORT_NAME_Copy Then

'Does not Work - the objects are different types
Set RPT = dbs.AllReports(REPORT_NAME_Copy)
'end of Does not Work

If RPT Is Nothing Then
OpenReport = False
On Error Resume Next
myDb.Close
Set myDb = Nothing
App.Quit acQuitSaveNone
Set App = Nothing
Exit For
Else
OpenReport = True
Exit For
End If
End If
Next obj
End With

Thank you,
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

[xzzy]

thanks, but setting the report object is the problem

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I haven't used this process with Access XP, but in Access 97 you
could use a similar method to get the name of a report from the
Reports Container, as you have with the AllReports object.

The problem is, you can't set a report object to it, since it isn't
open. All you have is a name. Add this line ...

DoCmd.OpenReport "[report name]",acViewDesign,,,acHidden

Once it's opened, then the next line, where you set a report object
to it, will work.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message

news:6jJBb.356152$275.1163344@attbi_s53...

I need to automate a report in a different database (and thank yous to Terry Kreft for pointing me in the right direction).

below is the code with the one line that does not work, marked: 'Does not Work'

It appears that the type of object returned from the "AllReports" collection is not a report object.
=>> I need it to be an object of type report.
'for automating remote report
Dim App As Access.Application
Dim myDb As Database
Dim dbs As Object
Dim RPT_Copy As Access.Report
Dim strDBPath As String
Dim obj As Object
Const REPORT_NAME_Copy = "MyReport"

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

With App
.OpenCurrentDatabase strDBPath, False
Set myDb = .CurrentDb
Set dbs = App.CurrentProject
For Each obj In dbs.AllReports
If obj.Name = REPORT_NAME_Copy Then

'Does not Work - the objects are different types
Set RPT = dbs.AllReports(REPORT_NAME_Copy)
'end of Does not Work

If RPT Is Nothing Then
OpenReport = False
On Error Resume Next
myDb.Close
Set myDb = Nothing
App.Quit acQuitSaveNone
Set App = Nothing
Exit For
Else
OpenReport = True
Exit For
End If
End If
Next obj
End With

Thank you,
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

[Danny J. Lesandrini]

Did you understand my answer? Is your problem solved already?

I agree, the problem is setting the report object, but the reason
it's a problemis because there is nothing to set it to. Your report
class doesn't exist, until you open the report, so you can't set the
report object equal to it without an error.

Did you try my suggestion, or are you dismissing it out of hand
because it seems incorrect to you?
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote ...

thanks, but setting the report object is the problem

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I haven't used this process with Access XP, but in Access 97 you
could use a similar method to get the name of a report from the
Reports Container, as you have with the AllReports object.

The problem is, you can't set a report object to it, since it isn't
open. All you have is a name. Add this line ...

DoCmd.OpenReport "[report name]",acViewDesign,,,acHidden

Once it's opened, then the next line, where you set a report object
to it, will work.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message

news:6jJBb.356152$275.1163344@attbi_s53...

I need to automate a report in a different database (and thank yous to Terry Kreft for pointing me in the right direction).

below is the code with the one line that does not work, marked: 'Does not Work'

It appears that the type of object returned from the "AllReports" collection is not a report object.
=>> I need it to be an object of type report.
'for automating remote report
Dim App As Access.Application
Dim myDb As Database
Dim dbs As Object
Dim RPT_Copy As Access.Report
Dim strDBPath As String
Dim obj As Object
Const REPORT_NAME_Copy = "MyReport"

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

With App
.OpenCurrentDatabase strDBPath, False
Set myDb = .CurrentDb
Set dbs = App.CurrentProject
For Each obj In dbs.AllReports
If obj.Name = REPORT_NAME_Copy Then

'Does not Work - the objects are different types
Set RPT = dbs.AllReports(REPORT_NAME_Copy)
'end of Does not Work

If RPT Is Nothing Then
OpenReport = False
On Error Resume Next
myDb.Close
Set myDb = Nothing
App.Quit acQuitSaveNone
Set App = Nothing
Exit For
Else
OpenReport = True
Exit For
End If
End If
Next obj
End With

Thank you,
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

[xzzy]

Using the above code in this thread, this means this breaks:

'make the column headers
Set ctlLabel = RPT![lblColumnHeader]
For i = 1 To intColumnCount

with an error of:

438 - object doesn't support this property or method
This works ( automating the report in the same Program MDB ), but automating
the report in the program MDB eventually causes the program MDB to become
corrupt.

'On Error Resume Next
''for now, just do it in the program MDB
'DoCmd.DeleteObject acReport, "MyReportCopy"
'DoCmd.CopyObject "", "MyReportCOPY", acReport, "MyReport"
'DoCmd.OpenReport "MyReportCOPY", acViewDesign
'Set RPT = Reports![MyReportCOPY]
'OpenReport = True
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I haven't used this process with Access XP, but in Access 97 you
could use a similar method to get the name of a report from the
Reports Container, as you have with the AllReports object.

The problem is, you can't set a report object to it, since it isn't
open. All you have is a name. Add this line ...

DoCmd.OpenReport "[report name]",acViewDesign,,,acHidden

Once it's opened, then the next line, where you set a report object
to it, will work.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message

news:6jJBb.356152$275.1163344@attbi_s53...

I need to automate a report in a different database (and thank yous to Terry Kreft for pointing me in the right direction).

below is the code with the one line that does not work, marked: 'Does not Work'

It appears that the type of object returned from the "AllReports" collection is not a report object.
=>> I need it to be an object of type report.
'for automating remote report
Dim App As Access.Application
Dim myDb As Database
Dim dbs As Object
Dim RPT_Copy As Access.Report
Dim strDBPath As String
Dim obj As Object
Const REPORT_NAME_Copy = "MyReport"

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

With App
.OpenCurrentDatabase strDBPath, False
Set myDb = .CurrentDb
Set dbs = App.CurrentProject
For Each obj In dbs.AllReports
If obj.Name = REPORT_NAME_Copy Then

'Does not Work - the objects are different types
Set RPT = dbs.AllReports(REPORT_NAME_Copy)
'end of Does not Work

If RPT Is Nothing Then
OpenReport = False
On Error Resume Next
myDb.Close
Set myDb = Nothing
App.Quit acQuitSaveNone
Set App = Nothing
Exit For
Else
OpenReport = True
Exit For
End If
End If
Next obj
End With

Thank you,
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

[xzzy]

Dan,

Thank you for your help, your advice of opening in design mode does enable
this assignment to happen:
Set RPT = dbs.AllReports(REPORT_NAME_Copy)

but the following works when it refers to a report in the local MDB and
breaks if it refers to a report in a different MDB ( I need to automate a
report that is in a different MDB ):
'make the column headers
Set ctlLabel = RPT![lblColumnHeader]
For i = 1 . . . . . . .
breaks with an error of:
438 - object doesn't support this property or method
Use this code to open it locally:

'On Error Resume Next
''for now, just do it in the program MDB
'DoCmd.DeleteObject acReport, "MyReportCopy"
'DoCmd.CopyObject "", "MyReportCOPY", acReport, "MyReport"
'DoCmd.OpenReport "MyReportCOPY", acViewDesign
'Set RPT = Reports![MyReportCOPY]
and all references to the RPT object work
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

Did you understand my answer? Is your problem solved already?

I agree, the problem is setting the report object, but the reason
it's a problemis because there is nothing to set it to. Your report
class doesn't exist, until you open the report, so you can't set the
report object equal to it without an error.

Did you try my suggestion, or are you dismissing it out of hand
because it seems incorrect to you?
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote ...

thanks, but setting the report object is the problem

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I haven't used this process with Access XP, but in Access 97 you
could use a similar method to get the name of a report from the
Reports Container, as you have with the AllReports object.

The problem is, you can't set a report object to it, since it isn't
open. All you have is a name. Add this line ...

DoCmd.OpenReport "[report name]",acViewDesign,,,acHidden

Once it's opened, then the next line, where you set a report object
to it, will work.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message

news:6jJBb.356152$275.1163344@attbi_s53...

> I need to automate a report in a different database (and thank yous

to Terry

> Kreft for pointing me in the right direction).
>
> below is the code with the one line that does not work, marked:

'Does not

> Work'
>
> It appears that the type of object returned from the "AllReports"

collection

> is not a report object.
> =>> I need it to be an object of type report.
>
>
> 'for automating remote report
> Dim App As Access.Application
> Dim myDb As Database
> Dim dbs As Object
> Dim RPT_Copy As Access.Report
> Dim strDBPath As String
> Dim obj As Object
> Const REPORT_NAME_Copy = "MyReport"
>
> strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
> Set App = New Access.Application
>
> With App
> .OpenCurrentDatabase strDBPath, False
> Set myDb = .CurrentDb
> Set dbs = App.CurrentProject
> For Each obj In dbs.AllReports
> If obj.Name = REPORT_NAME_Copy Then
>
> 'Does not Work - the objects are different types
> Set RPT = dbs.AllReports(REPORT_NAME_Copy)
> 'end of Does not Work
>
> If RPT Is Nothing Then
> OpenReport = False
> On Error Resume Next
> myDb.Close
> Set myDb = Nothing
> App.Quit acQuitSaveNone
> Set App = Nothing
> Exit For
> Else
> OpenReport = True
> Exit For
> End If
> End If
> Next obj
> End With
>
>
>
> Thank you,
> John Bickmore
> www.BicycleCam.com
> www.Feed-Zone.com
>
>

[Danny J. Lesandrini]

Here is part of the solution you're looking for. You created a new App object
and you want to use its methods to manipulate its objects

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

App.OpenCurrentDatabase strDBPath, False

App.DoCmd.OpenReport "rptRequests", acViewDesign, , , acHidden
Set rpt = App.Reports("rptRequests")

rpt.Caption = "New Report Caption Here"
App.DoCmd.Close acReport, "rptRequests", acSaveYes

--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message news:udLBb.493312$HS4.3783991@attbi_s01...

Using the above code in this thread, this means this breaks:

'make the column headers
Set ctlLabel = RPT![lblColumnHeader]
For i = 1 To intColumnCount

with an error of:

438 - object doesn't support this property or method
This works ( automating the report in the same Program MDB ), but automating
the report in the program MDB eventually causes the program MDB to become
corrupt.

'On Error Resume Next
''for now, just do it in the program MDB
'DoCmd.DeleteObject acReport, "MyReportCopy"
'DoCmd.CopyObject "", "MyReportCOPY", acReport, "MyReport"
'DoCmd.OpenReport "MyReportCOPY", acViewDesign
'Set RPT = Reports![MyReportCOPY]
'OpenReport = True
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com
"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br***********@ID-82595.news.uni-berlin.de...

I haven't used this process with Access XP, but in Access 97 you
could use a similar method to get the name of a report from the
Reports Container, as you have with the AllReports object.

The problem is, you can't set a report object to it, since it isn't
open. All you have is a name. Add this line ...

DoCmd.OpenReport "[report name]",acViewDesign,,,acHidden

Once it's opened, then the next line, where you set a report object
to it, will work.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"xzzy" <mr********@comcast.net> wrote in message

news:6jJBb.356152$275.1163344@attbi_s53...

I need to automate a report in a different database (and thank yous to Terry Kreft for pointing me in the right direction).

below is the code with the one line that does not work, marked: 'Does not Work'

It appears that the type of object returned from the "AllReports" collection is not a report object.
=>> I need it to be an object of type report.
'for automating remote report
Dim App As Access.Application
Dim myDb As Database
Dim dbs As Object
Dim RPT_Copy As Access.Report
Dim strDBPath As String
Dim obj As Object
Const REPORT_NAME_Copy = "MyReport"

strDBPath = Application.CurrentProject.Path & "\MyData.mdb"
Set App = New Access.Application

With App
.OpenCurrentDatabase strDBPath, False
Set myDb = .CurrentDb
Set dbs = App.CurrentProject
For Each obj In dbs.AllReports
If obj.Name = REPORT_NAME_Copy Then

'Does not Work - the objects are different types
Set RPT = dbs.AllReports(REPORT_NAME_Copy)
'end of Does not Work

If RPT Is Nothing Then
OpenReport = False
On Error Resume Next
myDb.Close
Set myDb = Nothing
App.Quit acQuitSaveNone
Set App = Nothing
Exit For
Else
OpenReport = True
Exit For
End If
End If
Next obj
End With

Thank you,
John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

[xzzy]

Danny,

Thank you for your help

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com