Why won't this code work?

[Mike]

Below is some code that I did with the help of an MVP on a slightly different
project. I keep getting a "File Not Found" error, and I can't see where I've
goofed it up. Can anyone help?

Private Sub Command3_Click()

Dim strOldName As String
Dim strNewName As String
Dim strOldFolder As String
Dim strNewFolder As String
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

strSQL = "SELECT ProductID FROM Products"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
strOldFolder = "C:\Documents and Settings\Mike\My Documents\Walfield\LNC
Pet Supply\a-e\"
strNewFolder = "C:\Documents and Settings\Mike\My Documents\Walfield\LNC
Pet Supply\ProductImages\"
strOldName = strOldFolder & rsCurr!ProductID & ".jpg" ' Define file names.
strNewName = strNewFolder & rsCurr!ProductID & ".jpg"
If (Len(Dir(strOldName)) > 0) Then
Name "strOldName" As "strNewName"
End If
rsCurr.MoveNext
Loop
End Sub

 

[Matthias Klaey]

Below is some code that I did with the help of an MVP on a slightly different
project. I keep getting a "File Not Found" error, and I can't see where I've
goofed it up. Can anyone help?

Private Sub Command3_Click()

Dim strOldName As String
Dim strNewName As String
Dim strOldFolder As String
Dim strNewFolder As String
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

strSQL = "SELECT ProductID FROM Products"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
strOldFolder = "C:\Documents and Settings\Mike\My Documents\Walfield\LNC
Pet Supply\a-e\"
strNewFolder = "C:\Documents and Settings\Mike\My Documents\Walfield\LNC
Pet Supply\ProductImages\"
strOldName = strOldFolder & rsCurr!ProductID & ".jpg" ' Define file names.
strNewName = strNewFolder & rsCurr!ProductID & ".jpg"
If (Len(Dir(strOldName)) > 0) Then
Name "strOldName" As "strNewName"

When you want to use variables in the Name statement,, enclose them in
parentheses:

Name (strOldName) As (strNewName)

End If
rsCurr.MoveNext
Loop
End Sub

HTH
Matthias Kläy

 

[Douglas J. Steele]

Name strOldName As strNewName

With what you had, Access was looking for a file named "strOldName", not for
the content of that variable.

 

[Douglas J. Steele]

When you want to use variables in the Name statement,, enclose them in
parentheses:

Name (strOldName) As (strNewName)

HTH
Matthias Kläy

No need for parentheses.

 

[Matthias Klaey]

No need for parentheses.

You are right.

It seems that somewhere deeply buried in me is a warning to always
force evaluation of expressions in statements like NAME or KILL.
I just checked: Access 1.x onward does not require parentheses,
neither does GWBASIC (1983 - 1986). The original 1964 BASIC of Kemeny
and Kurtz http://www.bitsavers.org/pdf/dartmouth/BASIC_Oct64.pdf
did not have a NAME statement (not even the NAMES of Kemeny and Kurtz
appear in the manual . So perhaps somwhere between 1970, when I
began programming, and 1983 there was a BASIC that did require
parentheses. Or perhaps I was just plain wrong for the past 35 or so
years?!?

Greetings
Matthias Kläy

 

[Douglas J. Steele]

You are right.

It seems that somewhere deeply buried in me is a warning to always
force evaluation of expressions in statements like NAME or KILL.
I just checked: Access 1.x onward does not require parentheses,
neither does GWBASIC (1983 - 1986). The original 1964 BASIC of Kemeny
and Kurtz http://www.bitsavers.org/pdf/dartmouth/BASIC_Oct64.pdf
did not have a NAME statement (not even the NAMES of Kemeny and Kurtz
appear in the manual . So perhaps somwhere between 1970, when I
began programming, and 1983 there was a BASIC that did require
parentheses. Or perhaps I was just plain wrong for the past 35 or so
years?!?

Couldn't tell you.

Realistically, putting parentheses around an argument to a sub or function
makes the argument ByVal, regardless of how it's declared, so in a case like
this, it shouldn't be a problem. In some sub or function calls, though, it
will be an issue.