mkdir basic help - create folder for each new record

[hstroud38]

Hi,



I want to create a folder for each new record in my access database. I understand that you need to use a mkdir statement, but I do not know where or how to do this. I can open VBA editor, but from there I am stuck.

Can someone please provide me with some basic instructions that can be understood by the laymen?

Thanks,

[TheShabz]

I don't know how you would do so but I'm extremely interesting in finding out why in the world you would want to do so.

[hstroud38]

I don't know how you would do so but I'm extremely interesting in finding out why in the world you would want to do so.

I need to create a directory for each record, in my case a distinct work order, where I can store photographs and invoices that are related with them. I do not wish to store these in the database itself in order to keep down size.

Does that answer your question?

This seems like a common question on forums, yet I haven't been able to find an explanation on how to do it that someone with limited knowledge of VBA can follow.

[orange]

I found this in some old code

'---------------------------------------------------------------------------------------
' Procedure : MakeDir
' DateTime  : 2005-06-28 10:20
' Author    :  
' Purpose   : To make a Directory from within VBA. It checks if
'             the Directory to be created already exists, and gives
'             an Error message if so.
'
'Parameters:
'sDrive  - the Drive on which the new directory is to be built
'sDir    - the new directoryName
'
'Note:   - Only creates 1 level per call
'        - the sDir must have leading \ for each level of directory
' eg  MakeDir "C" ,"\level1"         <--call 1
'     MakeDir "C" ,"\level1\level2"  <--call 2
'     will create c:\level1\level2  <--2 Calls required
'---------------------------------------------------------------------------------------
'
Sub MakeDir(sDrive As String, sDir As String)

On Error GoTo ErrorFound
VBA.FileSystem.MkDir sDrive & ":" & sDir
goto Exxit
ErrorFound:
If Err.Number = 75 Then
 MsgBox "Err 75 - Directory (" & sDrive & ":" & sDir & ") already exists"
Else
MsgBox Err.Number & " other error " & Err.Description
End If
Exxit:
End Sub

[hstroud38]

Hi Orange,

Thank you for the help. Unfortunately, I do not know where to place the code, or how to make it automatically run each time a new record is created. Essentially what I am looking for is this: I want to know how to make a VBA MkDir statement that will generate a folder each time a new record is created in the database and use a value from a field to name the folder. That is all.


Thanks again!

[R_Badger]

You ask that it be put so a lay person can understand it, sometimes a 'simple' task is not quite so simple, and vba is a necessity for all the odd or unusual things you might want to do (and also a lot of the common things you will want to do).

In this case, you want to create a new directory, in an unspecified location, for each new record in a table (database refers to the entire file essentially and therefore across multiple tables).

Oranges code will perform the required function. (It will need to be added to a visual basic module)

You need to place the code on a form, in the after insert event.
the line would read something like (as shown in oranges code in the comments):

MakeDir "C" ,"\level1"

[manic]

if you are saving the records as a PDF or any other file I use the following:

Private Sub CmdSavetoFile_Click()
On Error GoTo Err_LogErrors
Me.Refresh

Dim stDocName As String
Dim strPath As String

strPath = "L:\Operations\managers\" & DatePart("yyyy", Date_of_Log) & " Operator Log\ " & Me.Department & "-" & Me.Shift & "-" & "Shift"
If Dir(strPath, vbDirectory) = "" Then MkDir (strPath)

stDocName = "Report Operator Log"
DoCmd.OutputTo acReport, stDocName, acFormatPDF, strPath & "\" & Employee_Name & "_" & Format(Date_of_Log, "yyyymmdd") & ".pdf"


Exit_CmdSavetoFile:
Exit Sub
Err_LogErrors:
Call LogErrors(err.Number, err.Description, "CmdSavetoFile Failed,Operator Log Form")


Resume Exit_CmdSavetoFile

End Sub