Re: Error with DoCmd.Rename

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Rob Parker" wrote:

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" <limbim53 at yahoo dot com> wrote in message
news:E6ABC5E1-7AF0-4956-82A1-5D80130FE9A3@xxxxxxxxxxxxxxxx
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?



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Rob Parker" wrote:

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








.

Quantcast