VBA file folder location (Dynamic)

[Tango]

I have an access 2010 form that has VBA code to open a sharedrive location using it's network address i.e. \\SSC-FAS01\MATA\DBdocs\
It works like a champ regardless of who opens the link on our network. The problem is how do I enable people to change this address WITHOUT requiring anyone to edit the VBA code?

A form or prompt perhaps? Configuration INI file that stores the address? I prefer to do something along the line of a "scan all code and replace \\ssc-fas01\mata\DBdocs\ with \\ssc-fas40\stuff\DBdocs

If I can do this where from a form I can have them view the current default variable values and permanently change them that would be great. Any thoughts?

 

[boblarson]

I would just put it into a table (I use tsysPreferences) and then just pull it out when you need it.

 

[Tango]

I think that's what I am going to do but for curiosity sake is there a way to create an "error handler" that prompts for a new address if the existing one can't be found?

 

[boblarson]

You would just code it in when you code to get the value from the table.

So, like

Dim strPath As String
 
strPath = Nz(DLookup("MyFieldName", "myTableName", "PreferenceName='ServerLocation'), vbNullString) 
 
If strPath = vbNullString Then
   ... you would use a form, input box, or file browser to get the path and put it to strPath
End If

 

[Tango]

Private Sub Image97_Click()
[Forms]![frm107s].Requery
Dim strPath As String
If Nz(Me.[SubFrom], "") = "" Then
MsgBox "You must enter the submitting unit before accessing the folders."
Exit Sub
Else
MsgBox "Drop all files, pictures and other documents associated with this record into the window that is about to open."
strPath = ("[URL="file://\\ssc-cs-fas01\QAQAT\QP3\107"]\\ssc-cs-fas01\QAQAT\QP3\107[/URL] Submissions\") & ([ID]) & ([SubFrom]) & ("107 Submission")
    On Error GoTo MakeFolder_Err
    If Dir(strPath, vbDirectory) > vbNullString Then
    Call Shell("explorer.exe " & strPath, vbNormalFocus)
        Exit Sub
    End If
    MkDir strPath
    MakeFolder = True
    Call Shell("explorer.exe " & strPath, vbNormalFocus)
    Exit Sub
    End If
    
MakeFolder_Err:
    MsgBox "Folder """ & strPath & """ cannot be created."
    MakeFolder = False
End Sub

This is what I use right now. The code you suggested sounds like the perfect solution but how/where would I merge the two using the file exporer method like used above?

**BTW, Love your site, I am busy downloading your samples right now lol.

 

[boblarson]

Okay, first put this into a Standard Module (not form, report, or class module):

Function GetStoredLoc(strPrefName As String) As String
   GetStoredLoc = Nz(DLookup("PrefValue", "tsysPreferences", "PrefName='ServerLocation'"), GetFolder)
End Function
 
Function GetFolder() As String
Dim fd As FileDialog
 
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
 
With fd
    .AllowMultiSelect = False
    .ButtonName = "Get Folder"
    .Title = "Folder to Use"
    .Show
End With
 
GetFolder = fd.SelectedItems(1)
 
End Function

Then you need to make the table to store the value in and I would use
tsysPreferences
PrefID - Autonumber (PK)
PrefName - Text
PrefValue - Text

So then with a PrefName of ServerLocation:

Private Sub Image97_Click()
    Dim strPath As String
    Forms!frm107s.Requery
    If Nz(Me.SubFrom, vbNullString) = vbNullString Then
        MsgBox "You must enter the submitting unit before accessing the folders."
        Exit Sub
    Else
        MsgBox "Drop all files, pictures and other documents associated with this record into the window that is about to open."
        strPath = GetStoredLoc("ServerLocation") & (Me.ID) & (Me.SubFrom) & ("107 Submission")
        On Error GoTo MakeFolder_Err
        If Dir(strPath, vbDirectory) > vbNullString Then
            Call Shell("explorer.exe " & strPath, vbNormalFocus)
            Exit Sub
        End If
        MkDir strPath
        MakeFolder = True
        Call Shell("explorer.exe " & strPath, vbNormalFocus)
        Exit Sub
    End If
MakeFolder_Err:
    MsgBox "Folder """ & strPath & """ cannot be created."
    MakeFolder = False
End Sub

 

[Tango]

Sounds like a winner. Thanks so much for the help!