Create a sub folder from Access 2003

Posted on 2009-04-21
Last Modified: 2012-05-06
I have a MS Access 2003 membership database (operating system is Windows XP).  

In a form I want to be able to create a command button which sets up a sub folder in a specific drive to store documents related to that person.  

The file path for where the sub folder is to be created is: C:\ My Documents\CDG\Members Files\

The naming protocol for the subfolder would be [First Name]-[Last Name]-[ID] i.e. Dave-Smith-123

The end result in this example would therefore be a folder file path of : C:\ My Documents\CDG\Members Files\ Dave-Smith-123

If the subfolder already exists then I need a message box to come up and say Sub Folder Already Exists & doesnt try & create a new one.

Can anyone help?
Question by:correlate
    LVL 65

    Accepted Solution

    ok, try this

    dim sRoot as string
    dim sFolder as string

    sRoot = "C:\ My Documents\CDG\Members Files\"
    sFolder = Me.First_Name & "-" & Me.Last_Name & "-" & Me.ID

    if dir$(sRoot & sFolder, vbdirectory) <> "" then
        msgbox "Already exists"
        mkdir sRoot & sFolder
    end if
    LVL 65

    Expert Comment


    sFolder is just built up using string concatenation

    dir$(..., vbDirectory) returns empty string if it doesnt exist so we check to see if it is not, then display message otherwise we create the directory using mkdir

    Author Closing Comment

    Works a treat -thank you

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now