Create and link folder to command button (1 Viewer)

[Enginerd]

Hi,

I am creating a database where I want users to be able to store certain information in a folder on the computer and not in the database itself that is unique to them (for example, the folder is titled with their name). The folder would be created after the user's name is entered into the database and would be accessible by clicking on a command button on one of the forms. I have looked at multiple places and I cannot find code anywhere that will create a folder like that once the user's name is put into the database as well as how to link a command button to a computer folder using VBA code. Any input you can give is greatly appreciated.

 

[BeeJayEff]

Not sure if I fully understand the question, but have you looked at MkDir ?

 

[static]

This would create a user folder in C:\somefolder\

Private Const FOLDER As String = "C:\somefolder\"

Sub userfolder()
    Dim usrfldr As String
    Dim usrname As String
    
    'get user name
    usrname = Environ("username")
    If "" & usrname = "" Then
        MsgBox "error getting username"
        Exit Sub
    End If
    
    'get users folder
    usrfldr = FOLDER
    If Right(usrfldr, 1) <> "\" Then usrfldr = usrfldr & "\"
    usrfldr = usrfldr & usrname
    
    Debug.Print usrfldr
    
    'if folder doesn't exit, create it
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(usrfldr) Then
            newfolder = .createfolder(usrfldr)
        End If
    End With
End Sub

 

[The_Doc_Man]

BeeJayEff is correct. The other option is to activate a File System Object so that you can use FSO to manipulate the machine's file structure. (See static's contribution, for example.) However, the FSO is the more complex option and it might be more than you really need.

Just remember that if you want to create a folder, you are creating a path and you would therefore need to fully identify the path you wanted to create. It is not uncommon for someone using MkDir function to create a root-level folder because they forgot to include the full path they really wanted.

In my last job before retirement, about once every six months to a year, some yokel would forget that bit about supplying the full path and would end up making a root level folder such as C:\JohnQPublic when he really wanted C:\databasehome\JohnQPublic. We had security rules bout root-level folders and I usually had to clean up the mess in the aftermath. Then I had to go find the junior programmer who wrote the faulty code and give them a reminder that if I had not called them, the security people WOULD have called them. And like Agent K from Men In Black, when they visited they would NOT bring a sense of humor.

 

[Enginerd]

This would create a user folder in C:\somefolder\

Private Const FOLDER As String = "C:\somefolder\"

Sub userfolder()
    Dim usrfldr As String
    Dim usrname As String
    
    'get user name
    usrname = Environ("username")
    If "" & usrname = "" Then
        MsgBox "error getting username"
        Exit Sub
    End If
    
    'get users folder
    usrfldr = FOLDER
    If Right(usrfldr, 1) <> "\" Then usrfldr = usrfldr & "\"
    usrfldr = usrfldr & usrname
    
    Debug.Print usrfldr
    
    'if folder doesn't exit, create it
    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(usrfldr) Then
            newfolder = .createfolder(usrfldr)
        End If
    End With
End Sub

BeeJayEff, I don't know if this method will work for what I am trying to do. The database is set up so that each user has their own username and password for the database itself and the login is not based on the login for the computer. The reason for this is that the network we are using has one computer login for anyone logging into the computer. The database itself has to have a user specific username and password so that the user will only be able to access information that pertains to them. What I want to do is make it so that the username will serve as the folder name for the user's documents. What I am hoping is that the database can create the folder after update of the username field and then the user will click on a command button that will bring up that folder when clicked.

 

[static]

So replace

usrname = Environ("username")

with

usrname = dlookup(...)

or

usrname = forms!login!username

etc

 

[BeeJayEff]

BeeJayEff, ...

That's static's suggestion that you're responding to, not mine. However the basic logic of the surrounding code would be the same, just using MkDir instead of the With createobject ... filesystemobject .. block near the end.
Question for static - does the filesystemobject approach have any inherent advantage over MkDir ?

 

[The_Doc_Man]

When you don't have a useful domain-level login, that IS a tricky situation, but there is some hope. I found this as a way to get the OS user name.

http://access.mvps.org/access/api/api0008.htm

Look at it and see if it helps.