Question Looping Records to Create Directories

[Tim Bedborough]

Hi all, usual apology in case this has been done before (I'm sure it has!).

I have a recordset (the table is called tblJobTitle) and I want to loop through all the records and create a windows folder (MkDir) in my P directory with the name of the field/control [JobTitle].

This is what I have:

Private Sub Test_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblJobTitle")

Do Until rs.EOF
MkDir "P:" & Me.JobTitle
rs.MoveNext
Loop

End Sub

It will create the first record but then errors with a run time error 75 saying Path/File access error.

I have tried searching the blogs and the above looks OK to me but obviously not.

Help please, thanks.

 

[Tim Bedborough]

Oops sorry, MkDir "P:" & Me.JobTitle

I do have the slash in there just pasted it wrong

 

[moke123]

Private Sub Test_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblJobTitle")

Do Until rs.EOF
MkDir "P:" & Me.JobTitle
rs.MoveNext
Loop

End Sub

Where in your loop is the recordset field? Me.JobTitle is a form object.
I would also test for existing directory before making a new one.

 

[vba_php]

how bout this?

Private Sub Test_Click()

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblJobTitle")
             
    Do Until rs.EOF
          rs.movelast
          rs.movefirst
              if dir("P:\" & me.jobtitle.name) = "" then
                 MkDir "P:" & Me.JobTitle.name (".name" gives you the CONTROL name on the form.  without ".name", you get the value that's in that control at the time this code runs).
              end if
        rs.MoveNext
    Loop
        
End Sub

 

[Gasman]

Oops sorry, MkDir "P:" & Me.JobTitle

I do have the slash in there just pasted it wrong

This site removes "" unless it is in code or single quotes '\'

'P:\' & [YourfolderPath]

\

 

[Tim Bedborough]

Hi moke. I'm running the vba from a button on the form that houses the data. The form is a simple 2 field continuous form taken from the table tblJobTitle. Field 1 is the primary key and field 2 is called JobTitle. I tried using RecordsetClone and setting variables for directory name etc but everything ends up with not being able to move onto the next record in the table. I'm not sure this is what you are asking but it's about all I have unless you can make it a bit more simple please. Thanks.

 

[moke123]

More along these lines

Private Sub Test_Click()

    Dim rs As DAO.Recordset
    Dim DPath As String

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblJobTitle")

    Do Until rs.EOF

        DPath = "P:\" & rs!JobTitle

        Debug.Print DPath

        If Len(Dir(DPath, vbDirectory)) = 0 Then
            MkDir DPath
        End If

        rs.MoveNext
    Loop

End Sub

@Adam
wouldnt this be an endless loop? your moving first on each iteration.

    Do Until rs.EOF
          rs.movelast
          rs.movefirst
              if dir("P:\" & me.jobtitle.name) = "" then
                 MkDir "P:" & Me.JobTitle.name (".name" gives you the CONTROL name on the form.  without ".name", you get the value that's in that control at the time this code runs).
              end if
        rs.MoveNext
    Loop

 

[Tim Bedborough]

Hi vba. This produced 1 folder called Job Title

 

[Tim Bedborough]

Hi moke. Worked a treat. Thanks very much.