Create Folder based on field names

[dinsey90]

Hi,

I need my database to create a new folder on my computer and name it based on the fields within the form it comes from. I have included a picture of the form fields below. When i click the add quote button it saves the quote to the quote table. I need it to also create a folder on my computer and name it using the Quote_Num, Client_Name & Job_Name fields combined with a hyphen in between?

Thanks
J

[ssanfu]

Check out the MkDir Statement .

You would concatenate Quote_Num, Client_Name & Job_Name fields, then use that to create the new folder.

[dinsey90]

Sorry I'm pretty new to this, how would i go about concatenating the field names and creating the folder?

Thanks

[ssanfu]

You would use VBA to do something like

Dim MyQuotePath As String
Dim NewFolderName As String

 'Path to where you want to create the new folder
MyQuotePath = "C:\AllQuotes\dinsey90\"

'the new folder name
NewFolderName = Quote_Num & "_" & Client_Name & "_" & Job_Name 

'create the folder
MkDir MyQuotePath & NewFolderName

(doing this from memory )

[dinsey90]

Thanks ssanfu, i tried that but it is coming up with an error on the last line MkDir MyQuotePath & NewFolderName
Also will this create the folder name based on the details in the form that the user would be entering in to?

[aytee111]

Ssanfu's code works. Please post the entire subroutine that you are using.

[dinsey90]

Hi, Once i added it in i am now getting this error on both buttons in my form when trying to add a quote or just close without saving? I dont know how to resolve this, i tried just commenting out the code but it still occurs.

[aytee111]

I get that error sometimes too, not sure what causes it. I resolve it by going into form design, going to the event, and clicking on the three dots. This seems to reconnect the event procedure to the VBA module.

[dinsey90]

Hi aytee,

I've tried that a few times but no joy, it just keeps coming up every time i click the button?

[ssanfu]

These two lines should be at the top of every module

Option Compare Database
Option Explicit

Have you tried Debug /Compile?? The error should be in the "frmNewQuote" module.


BTW, in the lines

DoCmd.Close acForm, "frmNewQuote", acSaveYes

DoCmd.Close acForm, "frmNewQuote", acSaveNo

the acSaveYes & acSaveNo do not do what you think. These parameters are for saving DESIGN changes of the form, NOT saving the data.

In code, you could switch a form to design mode, change the form by adding or deleting an object, changing formatting, colors, etc and close the form. THEN you would use those parameters to save the form design change(s).......


You could post your dB for analysis......(do compact & repair, then zip it)

[dinsey90]

Thanks so much, the folder is now creating for me Is there anyway to create sub folders in this from the code? Thanks

[ssanfu]

Sure... create the new sub folder path, then use the MkDir command again.

[securitywyrm]

My code for something similar is

Private Sub Command69_Click()
If Len(Dir(CurrentProject.path & "\" & "RecordFiles" & "\" & Identification, vbDirectory)) = 0 Then
   MkDir CurrentProject.path & "\" & "RecordFiles" & "\" & Identification
End If
If Len(Dir(CurrentProject.path & "\" & "RecordFiles" & "\" & Identification & "\" & ID, vbDirectory)) = 0 Then
   MkDir CurrentProject.path & "\" & "RecordFiles" & "\" & Identification & "\" & ID
End If
Dim path As String
path = CurrentProject.path & "\" & "RecordFiles" & "\" & Identification & "\" & ID & "\"
Shell "cmd /C start """" /max """ & path & """", vbHide
End Sub

What this does: It checks if the first layer folder exists, if not then it creates it. THEN it checks if the second layer folder exists, and if not creates it, then opens the second layer folder.

And yes I know that this file structure is horrible because changing a person's name will break the link to all their files, but I have to write this assuming at some point in the future they're going to lose the access file and thus need to dig through all the files manually.