|
|
|
date: Thu, 26 Jun 2008 11:58:01 -0700,
group: microsoft.public.access.modulesdaovba.ado
back
Programmatically renaming reports
I have a number of reports that I want to rename programmatically.
They are all named "[reportnumber] HP Detail". I want to change that to "HP
Detail [reportnumber]"
The following code filters for reports with the proper name structure,
determines the [reportnumber] portion, then uses the docmd.rename method.
However, once the sub hits the rename line, it crashes. The error simply says
that it can't complete the operation. Error code 29068, which has no Help
dialogue.
Can anyone tell me why this is erroring, how should I correct, or is this
even possible?
Public Sub RenameReports()
Dim obj As AccessObject, Dbs As Object, txtName As String, oldName As String
q = 0
Set Dbs = Application.CurrentProject
For Each obj In Dbs.AllReports
oldName = obj.Name
If Right(obj.Name, 9) = "HP Detail" Then
Debug.Print oldName
q = InStr(obj.Name, " HP")
txtName = "Detail HP " & Left(oldName, q - 1)
Debug.Print txtName
DoCmd.Rename txtName, acReport, oldName
DoEvents
End If
Next obj
Set Dbs = Nothing
End Sub
date: Thu, 26 Jun 2008 11:58:01 -0700
author: WorldCTZen
RE: Programmatically renaming reports
Well, there is a problem with your methodology.
BTW, the first two lines of *every* code module should be:
Option Compare Database
Option Explicit
The line "Option Explicit" requires variables to be declared before the code
is run.
The problem in how you are renaming the Reports is explained here:
http://www.mvps.org/access/forms/frm0040.htm
This is for the Forms collection, but it applies to the Reports collection
also.
I modified your sub (and renamed it). I also wrote another sub using a DAO
recordset.
Here they are:
'-------------code beg--------------------
Public Sub RenameReports_ARC()
'
'Rename Reports using AllReports collection
'
Dim Dbs As Object
Dim newName As String
Dim oldName As String
Dim q As Integer
Dim intx As Integer
Dim intCount As Integer
Set Dbs = Application.CurrentProject
intCount = Dbs.AllReports.Count - 1
For intx = intCount To 0 Step -1
oldName = Dbs.AllReports(intx).Name
If Right(oldName, 9) = "HP Detail" Then
q = InStr(oldName, " ")
newName = "Detail HP " & Left(oldName, q - 1)
Debug.Print oldName, "=>", newName
DoCmd.Rename newName, acReport, oldName
DoEvents
End If
Next
Set Dbs = Nothing
End Sub
'-------------code end--------------------
'-------------code beg--------------------
Public Sub RenameReports_RS()
'
'Rename Reports using recordset
'
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim newName As String
Dim oldName As String
Dim q As Integer
strSQL = "SELECT MSysObjects.Name"
strSQL = strSQL & " FROM MsysObjects "
strSQL = strSQL & " WHERE (Left$([Name],1)<>'~')"
strSQL = strSQL & " AND (MSysObjects.Type) = -32764"
strSQL = strSQL & " AND Right(Name, 9) = 'HP Detail'"
strSQL = strSQL & " ORDER BY MSysObjects.Name;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
Debug.Print rs.RecordCount
Do While Not rs.EOF
oldName = rs.Fields(0)
q = InStr(oldName, " ")
newName = "Detail HP " & Left(oldName, q - 1)
Debug.Print oldName, "=>", newName
DoCmd.Rename newName, acReport, oldName
DoEvents
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'-------------code end--------------------
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
"WorldCTZen" wrote:
> I have a number of reports that I want to rename programmatically.
> They are all named "[reportnumber] HP Detail". I want to change that to "HP
> Detail [reportnumber]"
>
> The following code filters for reports with the proper name structure,
> determines the [reportnumber] portion, then uses the docmd.rename method.
> However, once the sub hits the rename line, it crashes. The error simply says
> that it can't complete the operation. Error code 29068, which has no Help
> dialogue.
>
> Can anyone tell me why this is erroring, how should I correct, or is this
> even possible?
>
> Public Sub RenameReports()
> Dim obj As AccessObject, Dbs As Object, txtName As String, oldName As String
> q = 0
> Set Dbs = Application.CurrentProject
> For Each obj In Dbs.AllReports
> oldName = obj.Name
> If Right(obj.Name, 9) = "HP Detail" Then
> Debug.Print oldName
> q = InStr(obj.Name, " HP")
> txtName = "Detail HP " & Left(oldName, q - 1)
> Debug.Print txtName
> DoCmd.Rename txtName, acReport, oldName
> DoEvents
> End If
> Next obj
> Set Dbs = Nothing
> End Sub
>
date: Tue, 1 Jul 2008 22:11:01 -0700
author: Steve Sanford limbim53 at yahoo dot com
Re: Programmatically renaming reports
It isn't clear why you are renaming your reports.
If this is related to ensuring that the proper title shows when the report
is printed, you don't need to do that by having multiple reports with
(nearly) identical reportnames.
If this is related to being able to see all the related reports collected in
the same general area, you can do this by collecting them in a group.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"WorldCTZen" wrote in message
news:9DFB2D3A-C29B-400F-95B2-769DF72B1D22@microsoft.com...
>I have a number of reports that I want to rename programmatically.
> They are all named "[reportnumber] HP Detail". I want to change that to
> "HP
> Detail [reportnumber]"
>
> The following code filters for reports with the proper name structure,
> determines the [reportnumber] portion, then uses the docmd.rename method.
> However, once the sub hits the rename line, it crashes. The error simply
> says
> that it can't complete the operation. Error code 29068, which has no Help
> dialogue.
>
> Can anyone tell me why this is erroring, how should I correct, or is this
> even possible?
>
> Public Sub RenameReports()
> Dim obj As AccessObject, Dbs As Object, txtName As String, oldName As
> String
> q = 0
> Set Dbs = Application.CurrentProject
> For Each obj In Dbs.AllReports
> oldName = obj.Name
> If Right(obj.Name, 9) = "HP Detail" Then
> Debug.Print oldName
> q = InStr(obj.Name, " HP")
> txtName = "Detail HP " & Left(oldName, q - 1)
> Debug.Print txtName
> DoCmd.Rename txtName, acReport, oldName
> DoEvents
> End If
> Next obj
> Set Dbs = Nothing
> End Sub
>
date: Mon, 7 Jul 2008 15:40:43 -0700
author: Jeff Boyce
Re: Programmatically renaming reports
PS. ... and if this is about making it easier, by the time you've designed,
tested, and verified the code to do this, you could have probably renamed
several dozen reports. How many do you have...?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"WorldCTZen" wrote in message
news:9DFB2D3A-C29B-400F-95B2-769DF72B1D22@microsoft.com...
>I have a number of reports that I want to rename programmatically.
> They are all named "[reportnumber] HP Detail". I want to change that to
> "HP
> Detail [reportnumber]"
>
> The following code filters for reports with the proper name structure,
> determines the [reportnumber] portion, then uses the docmd.rename method.
> However, once the sub hits the rename line, it crashes. The error simply
> says
> that it can't complete the operation. Error code 29068, which has no Help
> dialogue.
>
> Can anyone tell me why this is erroring, how should I correct, or is this
> even possible?
>
> Public Sub RenameReports()
> Dim obj As AccessObject, Dbs As Object, txtName As String, oldName As
> String
> q = 0
> Set Dbs = Application.CurrentProject
> For Each obj In Dbs.AllReports
> oldName = obj.Name
> If Right(obj.Name, 9) = "HP Detail" Then
> Debug.Print oldName
> q = InStr(obj.Name, " HP")
> txtName = "Detail HP " & Left(oldName, q - 1)
> Debug.Print txtName
> DoCmd.Rename txtName, acReport, oldName
> DoEvents
> End If
> Next obj
> Set Dbs = Nothing
> End Sub
>
date: Mon, 7 Jul 2008 15:42:07 -0700
author: Jeff Boyce
|
|
