Create a Folder based on Field in VBA

[Lochwood]

Hi,

I have code that can create a folder from a button based on the field name in Access and it works well.

The issue i have now is that the field i am using in the code has Data like 123/456/789 in which case it cannot create the folder.. is there a way in code i can substitute "/" with "-" so it can create the folder .. here is the code

Private Sub Command55_Click()
' Location of main folder
Const Parent = "N:\Document Library"
Dim Doc_Number As String
Dim Folder As String
Dim fso As Object
' Get Main ID from control
Doc_Number = Me.Doc_Number
' Full path
Folder = Parent & Instrument_Number
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check whether folder exists
If fso.FolderExists(Folder) = False Then
' If not, create it
fso.CreateFolder Folder
End If
' Open it
Shell "explorer.exe " & Folder, vbNormalFocus
End Sub

 

[isladogs]

Add this line before create file system object

Folder=Replace(Folder,"/","-")

 

[Lochwood]

Great, worked a treat. I have attached the full code for reference:

Private Sub Command55_Click()
' Location of main folder
Const Parent = "N:\Document Library"
Dim Doc_Number As String
Dim Folder As String
Dim fso As Object
' Get Main ID from control
Doc_Number = Me.Doc_Number
' Full path
Folder = Replace(Parent & Doc_Number, "/", "-")
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check whether folder exists
If fso.FolderExists(Folder) = False Then
' If not, create it
fso.CreateFolder Folder
End If
' Open it
Shell "explorer.exe " & Folder, vbNormalFocus
End Sub

 

[moke123]

Just a heads up.
While I don't see that "Parent" is specifically a reserved word, it is a form property so I would err on the side of caution and consider changing the name.

 

[isladogs]

Moke makes a very good point.

Daniel Pineault has made a very useful reserved word checker utility which is available here:

https://www.devhut.net/2017/06/21/ms-access-reserved-word-checker/

'Parent' is included in his list as an application property