Programmatically renaming reports

[WorldCTZen]

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

 

[Steve Sanford]

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.)

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

 

[Jeff Boyce]

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

 

[Jeff Boyce]

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