Renaming a file using an input form

[LarryE]

I have a table field that the user can input text into using a Form.
I want to use: Name oldfilename As NewFileName
action to change the name of a file based on what they input.
I am having a terrible time getting the Name action to read (lookup) the
table field name.
I am getting runtime error 53 'File Not Found'
Here is my code:

Dim OldFileName, NewFileName As String
OldFileName = "C:\ORCAS Backup\BackupORCAS.accdb"
NewFileName = "C:\ORCAS Backup\" & DLookup("[BackupFileName]", "Company Name
Table", "[BackupFileName]=Forms![Backup Data Form]![BackupFileName]") &
".accdb"
Name OldFileName As NewFileName

So if the user inputs 'Johns Backup', the OldFileName should be changed to
Johns Backup.accdb
The table field information is in: Tables![Company Name
Table]![BackupFileName]
I also tried referencing the table field name instead of DLookup(), but I
get the same error.

Can anyone help me with the syntax to reference the table field in the Name
action?
Thank you.

 

[Brian]

Try this. There are some quotes missing in the DLookup, and the two MsgBox
entries will show you if things are resolving to the correct strings.

Post the results of each of the InputBox entries if this does not work, as
that should reveal any syntax errors in the string concatenation. (Using an
InputBox will place the value of each where you can copy the string & paste
back here).

Dim OldFileName, NewFileName As String
OldFileName = "C:\ORCAS Backup\BackupORCAS.accdb"
NewFileName = "C:\ORCAS Backup\" & DLookup("[BackupFileName]", "[Company Name
Table]", "[BackupFileName] = '" & Forms![Backup Data Form]![BackupFileName]
& "'") &
".accdb"
Dim strName as String 'just used to capture results in InputBox below
strName = InputBox("OldFileName",OldFileName)
strName = InputBox("NewFileName",NewFileName)
Name OldFileName As NewFileName

 

[LarryE]

Brian, thank you for your quick reply. The code you suggested gave me the
error as well BUT here is what I found:
1. I needed a Refresh command after I input the name in my form.
2. I was trying to input the text: Larry 12/30/2009 and the 12/30/2009
formatting was causing the error. If I input Larry 12302009 it worked, or
anything else as long as it wasn't a formatted date. Very weird. If I can Dim
the NewFileName as some other format I wonder if it would accept the
12/30/2009 date format. I don't know. The table field is formatted for text
so maybe it can't recognize the / as text. Anyway it works now. thanks again
for your kind assistance.



--
Larry

Try this. There are some quotes missing in the DLookup, and the two MsgBox
entries will show you if things are resolving to the correct strings.

Post the results of each of the InputBox entries if this does not work, as
that should reveal any syntax errors in the string concatenation. (Using an
InputBox will place the value of each where you can copy the string & paste
back here).

Dim OldFileName, NewFileName As String
OldFileName = "C:\ORCAS Backup\BackupORCAS.accdb"
NewFileName = "C:\ORCAS Backup\" & DLookup("[BackupFileName]", "[Company Name
Table]", "[BackupFileName] = '" & Forms![Backup Data Form]![BackupFileName]
& "'") &
".accdb"
Dim strName as String 'just used to capture results in InputBox below
strName = InputBox("OldFileName",OldFileName)
strName = InputBox("NewFileName",NewFileName)
Name OldFileName As NewFileName

I have a table field that the user can input text into using a Form.
I want to use: Name oldfilename As NewFileName
action to change the name of a file based on what they input.
I am having a terrible time getting the Name action to read (lookup) the
table field name.
I am getting runtime error 53 'File Not Found'
Here is my code:

Dim OldFileName, NewFileName As String
OldFileName = "C:\ORCAS Backup\BackupORCAS.accdb"
NewFileName = "C:\ORCAS Backup\" & DLookup("[BackupFileName]", "Company Name
Table", "[BackupFileName]=Forms![Backup Data Form]![BackupFileName]") &
".accdb"
Name OldFileName As NewFileName

So if the user inputs 'Johns Backup', the OldFileName should be changed to
Johns Backup.accdb
The table field information is in: Tables![Company Name
Table]![BackupFileName]
I also tried referencing the table field name instead of DLookup(), but I
get the same error.

Can anyone help me with the syntax to reference the table field in the Name
action?
Thank you.