Create Folder Bases on Two Fields

[dancaw]

Hi



Is it possible use a button to create a folder named from two fields:

"txt_TenderNo" & "txt_PrejectTitle"

and saved in an existing directory "z:\Estimate"

what would also be great is if i could use following folder as template

z:\Estamate\EstimateStructure

Ive kinda done something similar in a batch code

code i used on that was:

SET /P fold= Enter The Project Name:
CHOICE /M "Is this correct: %Z:\Estimates\%"
IF %ERRORLEVEL% equ 2 CLS & GOTO start


robocopy "Z:\Estimates\EstimateStructure" "%~dp0\%fold%" /MIR /SEC /SECFIX /XX /A-:H >nul 2>nul


Trying make like quicker and simpler in long run

[kd2017]

You can concatenate the to fields into a full path something like this:

Dim new_dir As String
new_dir = "z:\Estimate\" & Me.txt_TenderNo & " - " & Me.txt_PrejectTitle

'---------------- do some error checking here to make sure the folder doesn't already exist, valid folder names, etc.

Have you tried this?
https://docs.microsoft.com/en-us/off...yfolder-method
https://www.rondebruin.nl/win/s3/win026.htm
https://www.google.com/search?q=vba+copy+folder

[kd2017]

And here is a link to some code you can use to check if files or folders already exist: http://allenbrowne.com/func-11.html

[dancaw]

Thanks for the feed back,

Found something that do the job but getting compile errors

https://www.devhut.net/2010/11/14/ms...copy-a-folder/

'---------------------------------------------------------------------------------------
' Procedure : CopyFolder
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Copy a folder
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFolderSource Folder to be copied "Z:\Estimates\EstimateStructure"
' sFolderDestination Folder to copy to "Z:\Estimates"
' bOverWriteFiles Whether to overwrite file(s) if the folder already exists
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' CopyFolder("Z:\Estimates", "Z:\Estimates\EstimateStructure", True)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' ************************************************** ************************************
' 1 2010-Nov-14 Initial Release
'---------------------------------------------------------------------------------------
Function CopyFolder(sFolderSource As String, sFolderDestination As String, _
bOverWriteFiles As Boolean) As Boolean
On Error GoTo Error_Handler
Dim fs As Object

CopyFolder = False
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFolder sFolderSource, sFolderDestination, bOverWriteFiles
CopyFolder = True

Error_Handler_Exit:
On Error Resume Next
Set fs = Nothing
Exit Function

Error_Handler:
If Err.Number = 76 Then
MsgBox "The 'Source Folder' could not be found to make a copy of.", _
vbCritical, "Unable to Find the Specified Folder"
Else
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: CopyFolder" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
End If
Resume Error_Handler_Exit
End Function

also not sure where to put Varibles [Tender_No] - [Project_Title]

Regards

Dan

[kd2017]

It would be more helpful to us if you can provide the exact error messages that your getting when you need help on them.

I ran that snippit of code on my machine and it works fine. Maybe you need to add the reference to the Microsoft Office Objecty Library? I think not though because it looks like late binding, also you should have already done that given the context from your other recent thread... To diagnose we'll need to know what your error messages are saying.

This is how you *might* use that function:

Private Sub Command3_Click()
    CopyFolder "Z:\Estimates\EstimateStructure", "Z:\Estimates\" & Me.txt_TenderNo & " - " & Me.txt_PrejectTitle, False
End Sub

[dancaw]

Thanks for your Help,

Attached is screen shots,

Field Names are actually Tender_No and Project_Title

but i changed the code to suit and still having issues

[kd2017]

The code that I used referred to the form's text box control names which I assumed from post #1. Because you're referring to a field from the form's record source try replacing the dot in Me. with an exclamation point like this:

Private Sub Command29_Click()
    CopyFolder "Z:\Estimates\EstimateStructure", "Z:\Estimates\" & Me!Tender_No & " - " & Me!Project_Title, False
End Sub

I still don't fully grasp the dot and bang notation myself
https://stackoverflow.com/questions/...-and-ms-access

[dancaw]

Hi Thanks for Trying,

Still getting Same Issue and i have also tried the fields into speech marks no luck

[kd2017]

can you zip your db file and post it here for review? If you need to make a copy and delete any confidential data from the copy.

[Micron]

I still don't fully grasp the dot and bang notation myself

! provides late bound access to the default property of a control by passing the word (name) following ! as the .Name argument. The ONLY place I can think of that I would use ! is with recordsets. If I want late bound Access, I probably wouldn't Dim the object using the specific library (e.g. use As Object instead). An invalid reference (e.g. Me!txtReciept instead of Me.Receipt) will not compile and will fail at run time.

The error is probably a missing reference as you say, but I think that would be for the Scripting. Probably Microsoft Scripting Runtime

[accesstos]

I still don't fully grasp the dot and bang notation myself

Let's say you have a table like this:



With a code like this:

Sub TestNotation()
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblBoxes")
    
    Debug.Print "Name of recordset is: '" & rs.Name & "'"
    Debug.Print "Name of Box is: '" & rs!Name & "'"
    Debug.Print "Type of recordset is: " & rs.Type
    Debug.Print "Type of Box is: '" & rs!Type & "'"
    'Debug.Print "Length of recordset is: " & rs.Length 'Compile Error
    Debug.Print "Length of Box is: " & rs!Length
    
    rs.Close
    Set rs = Nothing
End Sub

you get the results below:

Name of recordset is: 'SELECT * FROM tblBoxes'
Name of Box is: 'Box1'
Type of recordset is: 2
Type of Box is: 'Pack'
Length of Box is: 120

rs.Name is the name of the recordset.
rs!Name is the value of the field [Name]

I hope it's more clear now.

[Micron]

So that is proof of what I wrote, I guess, but in a round about way?

[kd2017]

Thanks for the explanation fellas! But being honest this is where my confusion comes from: http://access.mvps.org/access/forms/frm0031.htm
It is more clear now. I need to sit down and play with it hands on now to flesh it out for myself.

Sorry OP to derail your thread. Hopefully you too learn something from this like I have ?? Post your DB when you get a chance and we'll get you running.

[Micron]

You lost me. What does that have to do with bang vs dot?
If you can already grasp what I wrote to explain it, then you're doing better than me. Took me a while before it sank in. I mean, I understood it the first time, but it didn't stick right away

Ditto re apologizing to dancaw, but maybe he/she is learning something regardless.
In the meantime, I'll refrain until the db is posted as it's impossible to say at this point if the error is a missing reference, mis-spelled control name or a malformed path string. To test the latter, I'd create a variable, build the string and then debug.print to the immediate window and see if it's properly formed.

[kd2017]

You lost me. What does that have to do with bang vs dot?

Extreme example from link:
Forms!Mainform!Subform1.Form!Subform2.Form!Control Name.Enabled
Bang bang dot bang dot bang dot *hit head with hammer*