Error with DoCmd.Rename

[Rob Parker]

I'm trying to rename a report (which is not open) prior to sending it via
SendObject; I'm doing this so that the .snp file has a meaningful name to
the receipient.

According to the Help file, I should be able to do this quite simply:
<quote from help file for Rename method>
expression.Rename(NewName, ObjectType, OldName)
....
NewName Required Variant. A string expression ...

The following example renames the Employees table.

DoCmd.Rename "Old Employees Table", acTable, "Employees"
<end quote>

My code is as follows:
...
Dim strStaff As String
...
'rename report to staff name
DoCmd.Rename strStaff, acReport, "rptTimetable"
'send report
DoCmd.SendObject acSendReport, strStaff, acFormatSNP, , , , "Semester
Timetable", strEmailText, True
'rename report to default name
DoCmd.Rename "rptTimetable", acReport, strStaff
...

When I entered the code, intellisense gives acReport as a valid parameter
for the object type. strStaff contains a string which is a valid name for a
report (eg "HILLIARK").

When the code runs, I get Error 29068 "TimeTables cannot complete this
operation. You must stop the code and try again." when it gets to the first
DoCmd.Rename statement. Changing the declaration of strStaff to Variant has
no effect (and I don't believe it should be necessary to do so anyway).
Opening the report prior to the Rename command gives (as I expected) a
different error, Error 2009 "You can't rename the database object
'rptTimetable' while it's open."

Why does DoCmd.Rename fail? And, if I can't get this to work, how can I
rename the report before sending it?

TIA,

Rob

 

[SteveS]

Hi Rob,

I'm running Win2K/Acc2K. I modified your code a little....

'-----------------
Private Sub Command9_Click()

Dim strStaff As String

strStaff = "HILLIARK"

'rename report to staff name
DoCmd.Rename strStaff, acReport, "rptTimetable"

MsgBox "report renamed to " & strStaff

'rename report to default name
DoCmd.Rename "rptTimetable", acReport, strStaff

MsgBox "report renamed to rptTimetable"

End Sub
'------------------

I don't get any errors.

How are you setting the value for strStaff?

 

[Rob Parker]

Hi Steve,

Thanks for the response. Your test result is what I was expecting

I'm setting strStaff from a list box selection, and then using a function to
remove any possible invalid characters from the string. The code in the
command button click event (to send email messages to each selected staff)
is:

Dim strStaff As String
Dim varItem As Variant
...
For Each varItem In Me.lstStaff.ItemsSelected
strStaff = Me.lstStaff.ItemData(varItem)
strStaff = RemoveBadChars(strStaff) ***
...
'code as in previous posting
...
Next varItem

The rowsource for the listbox is a query which simply selects distinct staff
from a table; the datatype for the staff field in the underlying table is
text.

*** the RemoveBadChars function was not included in my code when I first
posted; at that stage, I was sure that the entries in the listbox were all
valid as object names. I've since added that to make it bullet-proof - if
there's such a thing ;-) - for the end-users. The function code is:

Function RemoveBadChars(ByVal strSource As String)
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblInvalidChars", dbOpenDynaset)
Do While Not rs.EOF
strSource = Replace(strSource, rs!InvalidChar, "")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
RemoveBadChars = strSource
End Function


BTW, I'm running Access2002 (Access2000 file format) under Win XP Pro. My
application will be run by end-users with Access2003.

Any further thoughts would be greatly appreciated. At present, as a
workaround, I'm copying the report to give it a new name, and the deleting
the copy (and the database is set to Compact on Close).

Rob

 

[SteveS]

Rob,

Did you try running the code in my previous post? Was the report renamed??

I had the form and the database window (report tab selected) visible at the
same time so I could watch the report name to be sure it was renamed.

If that works, then try this code

'----------

Dim strStaff As String
Dim varItem As Variant

strStaff = ""
For Each varItem In Me.lstStaff.ItemsSelected
strStaff = Me.lstStaff.ItemData(varItem)
MsgBox strStaff
'rename report to staff name
DoCmd.Rename strStaff, acReport, "rptTimetable"

MsgBox "report renamed to " & strStaff

'rename report to default name
DoCmd.Rename "rptTimetable", acReport, strStaff
MsgBox "report renamed to rptTimetable"
Next varItem

'----------------

If the above code works, in your original code, select the first line
(Sub....) and press the F9 key. This sets a breakpiont in the code. (the line
should turn brown with a brown dot in the left.

When you run the code, the debug window will appear with the code paused at
the breakpoint. Press the F8 key to advance one line at a time.

One of the lines will cause the error. What line does it fail at?

 

[Rob Parker]

Hi Steve,

I must admit that I didn't actually run the simplified routine from your
first post; I just told you how I was setting the value of strStaff.

I've just tried the simple routine, and it fails, with the error I posted
originally.

I can rename the report manually (in the database window).

And, as a result of some more actions while I'm writing this, I find that
the simple code now works! What I did was run the code, and while it was
stopped at the error line (the first DoCmd.Rename), I opened the report,
then closed it (at which time I got a message that this would stop the
debugger). Subsequently, the code runs OK.

However, replacing the simple code with the code from your latest post
(which seems to be functionally identical to my original code) failed, with
the same error on the first DoCmd.Rename line.

In desparation, I've just done a /decompile on my database, then
re-compiled. Everything now seems sane, and both your second code posting,
and my original code, now work (consistently, multiple times).

I've no idea what the problem was, but it's gone. Thanks for your help (in
particular, checking that my initial code really does work!!!).

Rob