Loop

[mlr0911]

Hello all, hope that you are having a Great Day!

I hopeing that someone could help me with a Loop problem. I am trying to create a directory on each name change. My code works for the first name, however, when I am trying to Loop through the table, it isn't working.

Here is my code

'MkDir "H:\NewPrivateFolder"

On Error GoTo Err_cmbMkDir_Click

Dim DirName As String
Dim response As String
Dim folder As String


folder = "H:\Projects\"


'Forms("form1").Controls("list1").MultiSelect = 2
'DLookup("Officers_Name", "tblOfficers")
DirName = folder & DLookup("officers_name", "tblofficers")


Do
If Dir(DirName, vbDirectory) = "" Then


If MsgBox("OK to create folder!", vbOKCancel) = vbOK Then

MkDir DirName

Else
MsgBox "Create folder cancelled. Folder not created."
Exit Sub
End If
Else
MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
Exit Sub
End If
'response = MsgBox(DirName, vbOKOnly)

Loop

Exit_cmbMkDir_Click:
Exit Sub

Err_cmbMkDir_Click:
MsgBox Err.Description
Resume Exit_cmbMkDir_Click


End Sub

 

[Dennisk]

just a quick question
are you trying to loop through all the records in tblOfficers and create a folder for each officer? If so then then you are using the wrong technique completely.

 

[mlr0911]

Dennisk,

Yes, that is exactly what I am trying to do. What would you suggest?

 

[Dennisk]

use a recordset to read all records like this

dim rst as DAO.recordset
on error goto Err_Handler

set rst=currentdb.Openrecordset("tblofficers")

do until rst.eof
' Process each officer here
rst.movenext
loop

exit_handler:
rst.close
set rst=nothing
exit sub

Err_Handler:
msgbox err & " " & err.description
resume Exit_Handler

end sub

 

[mlr0911]

Where would i put my code to create the directory? I keep getting an Compile error "Loop without Do".

At ' Process each officer here, that's where I put my DIR information, correct?

Thanks again

 

[mlr0911]

When I add mkDIR dirname to the code, it will not go to the next recordset. I stops everytime on the record it sees first. What am I doing wrong?

THanks

 

[pbaldy]

If you expect someone to debug your code, it usually helps to post the code. This is one of those times.

 

[mlr0911]

Paul-

Here is the code that I am using with dennisk's help. I need to create mulitple directories based off of the tblofficers. Thanks for your help.






Private Sub command13_click()

Dim rst As DAO.Recordset
Dim DIRName As String
Dim folder As String

folder = "H:\Projects\"
DIRName = folder & DLookup("officers_name", "tblofficers")

Set rst = CurrentDb.OpenRecordset("tblofficers")

Do Until rst.EOF

MkDir DIRName

' Process each officer here
rst.MoveNext

Loop



End Sub

 

[pbaldy]

It's obvious if you stop and think about it. You set the variable DIRName before the loop ever starts, so why would it ever change during the loop? It just tries to keep creating the same folder. You need to set it within the loop, using the value from the recordset, not from a DLookup.

 

[mlr0911]

Paul

WHen I try to set the value to the recordset, it wont continue.

 

[pbaldy]

Hate to be repetitive, but can you post that code?

 

[mlr0911]

Dim rst As DAO.Recordset
Dim DIRName As String
Dim folder As String

folder = "H:\Projects\"
DIRName = folder & rst
Set rst = CurrentDb.OpenRecordset("tblofficers")

Do Until rst.EOF

MkDir DIRName

' Process each officer here
rst.MoveNext

Loop

 

[pbaldy]

Dim rst As DAO.Recordset
Dim DIRName As String
Dim folder As String

folder = "H:\Projects\"

Set rst = CurrentDb.OpenRecordset("tblofficers")

Do Until rst.EOF
DIRName = folder & rst!officers_name
MkDir DIRName

' Process each officer here
rst.MoveNext

Loop

 

[mlr0911]

Paul

Gees, I can't believe I missed that......thanks for your help.