I think that line should read
If Len(Dir(strDesktop & strSubfolder, vbDirectory)) = 0 Then
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
NewSysAdmin wrote:
Thank you for the explanation. It makes a little more sense to me now
and I
understand why it keeps trying to creat a folder. The error I'm
getting on
your code is "Syntax Error" on the following line:
If (Len(Dir(strDesktop & strSubfolder), vbDirectory) = 0) Then
It wants to put a ) where the comma is before vbDirectory. Any ideas?
:
Len( SomeString ) returns the length of a string.
Dir( SomeFilePath ) returns the name of the file, minus the folder
bits, if
the file exists, otherwise it returns a ZLS (zero-length string)
Dir( SomeFolderPath, vbDirectory ) returns the name of the folder,
minus all
the parent folders, if the folder exists, otherwise it returns a ZLS.
So, Len( Dir( ... ) ) will return zero if the file or folder does NOT
exist.
Now, Len(Dir(strDesktop, vbDirectory)) will never return zero, because
your
desktop always exists.
Len( strSubfolder ) will always return zero, unless the subfolder
exists in
your *current* directory (probably My Documents).
NonZero AND Zero always results in zero, so your code will always
attempt to
create the subfolder, whether or not it exists.
The code I gave you *should* work, provided there are no illegal
characters
in the name of the folder you are trying to create. What was the
error you
got?
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Thank you. I had to modify the If(Len) statement a little because I
was
getting an error on it.
I want to make sure I'm understanding the Len function correctly.
When the
Len function= 0, I think that means the folder does not exist,
correct?
Now it will create a folder for the record, but when clicked again,
it
gives
me "error 75 path/file access error" because it's trying to create
the
same
folder again. It will not skip to the Else statement and give an
error
message. What am I doing wrong?
If Len(Dir(strDesktop, vbDirectory)) And Len(Dir(strSubfolder,
vbDirectory))
= 0 Then
MkDir strDesktop & strSubfolder
MsgBox "The folder has been created"
Else
MsgBox "ERROR: THE FOLDER ALREADY EXISTS"
End If
:
If all the data you need to create the folder are available in the
current
record on your form, then you don't need any recordset or SQL
statement:
You will need something like this:
Private Sub MakeFolder_Click()
Dim strDesktop As String
Dim strSubfolder As String
strDesktop = "C:\Documents and Settings\eherrera\Desktop\"
strSubfolder = Me![Customer Model Code] & "-" _
& Me![Second field] & "-" _
& Me![Third field]
If (Len(Dir(strDesktop & strSubfolder), vbDirectory) = 0) Then
MsgBox ("Error: The folder already exists")
Else
MkDir strDesktop & strSubfolder
End If
End Sub
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
message
Thank you for the quick response. I have a command button that the
user
will
click which will create a folder for only that specific record. I
took
out
the loop statement, as this is an existing database, and we do not
need
folders for all of the old records.
The code worked, but I would like to change the folder name to
include
2
other fields with dashes between them, if possible. I'm getting a
type
mismatch error 13 on the strSQL line.
I would also like to display a message box if the folder already
exists.
I'm sorry that my code is sloppy as my knowledge of Visual Basic is
minimal
at best. My code is below. Thanks again for any further help you
can
provide.
Private Sub MakeFolder_Click()
Dim rs As DAO.Recordset
Dim strDesktop As String
Dim strSQL As String
strDesktop = "C:\Documents and Settings\eherrera\Desktop\"
strSQL = "SELECT [Customer Model Code] as Folder" _
& " FROM [RMA/TR Tracking table]"
' if necessary, add a WHERE clause to select the record(s)
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
If (Len(Dir(strDesktop)) > 0) Then
MsgBox ("Error: The folder already exists")
rs.Close
End If
If (Len(Dir(strDesktop)) = 0) Then
MkDir strDesktop & rs!Folder
rs.MoveNext
rs.Close
End If
End Sub
:
Do you want to create a folder for *every* record in your table?
If
not,
then how do you select the record(s)?
Try some code like this:
Dim rs as DAO.Recordset
Dim strDesktop as String
Dim strSQL as String
strDesktop = "C:\Documents and Settings\test\desktop\"
strSQL = "SELECT [Customer Model Code] as Folder" _
& " FROM [RMA/TR Tracking table]"
' if necessary, add a WHERE clause to select the record(s)
Set rs = CurrentDb.OpenRecordset( strSQL, dbOpenForwardOnly )
Do Until rs.EOF
MkDir strDesktop & rs!Folder
rs.MoveNext
Loop
rs.Close
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
message
Hello,
I'm trying to create a folder on my desktop by selecting values
in a
field
(Customer Model Code) from a table and using that field as the
folder
name.
I thought it might be easier to create the folder, and then
rename
it.
Please see my code below. I'm getting an error on the Name line-
"file
not
found". Can anyone offer any help with this? Is there a better
way
to
approach it? Thank you very much.
Private Sub MakeFolder_Click()
Dim strSQL As String
Dim strOldFolder As String
MkDir "C:\Documents and Settings\test\desktop\test"
strOldFolder = "C:\Documents and Settings\test\desktop\test"
strSQL = "SELECT [Customer Model Code] FROM [RMA/TR Tracking
table]"
Name strOldFolder As strSQL
End Sub
