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 7 2448
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
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
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
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
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 > >
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
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
7 posts
views
Thread by Limey Drink |
last post: by
|
1 post
views
Thread by EnriqueM |
last post: by
|
reply
views
Thread by Randyb |
last post: by
|
4 posts
views
Thread by Supa Hoopsa |
last post: by
|
6 posts
views
Thread by Edgar |
last post: by
|
2 posts
views
Thread by Dave |
last post: by
| | | | | | | | | | | | | |