'Used for obtaining ClientDataPath Dim ClientDataPath As Variant Dim DB As DAO.Database Dim DBRS As DAO.Recordset Dim SQL 'Get path to store client files Set DB = CurrentDb SQL = "SELECT ClientDataPath FROM AppConfig" Set DBRS = DB.OpenRecordset(SQL, dbOpenDynaset) ClientDataPath = DBRS("ClientDataPath") 'Ensure the configuration has a ClientDataPath entered If IsNull(ClientDataPath) = True Then 'No path entered, get one ClientDataPath = InputBox("No path has been set to store client files." & vbCrLf & "Enter a root path to store client files below." & vbCrLf & "Files will subsequently be stored in a sub folder named after the location ID.") 'Ensure the path entered includes a \ at the end. If Trim(Right(ClientDataPath, 1)) <> "\" Then ClientDataPath = Trim(ClientDataPath) & "\" 'Check if the folder exists and create if necessary. If FolderExists(ClientDataPath) = False Then MsgBoxAns = MsgBox("Path does not exist. Create it now?", vbYesNo) If MsgBoxAns = 6 Then 'Create the path. CreateFolder (ClientDataPath) Else 'Fail and cancel out of sub. MsgBox "Unable to add file.", vbExclamation + vbOKOnly Exit Sub End If Else 'Update the AppConfig table with the new path. DoCmd.RunSQL "UPDATE AppConfig SET ClientDataPath = '" & ClientDataPath & "'" End If End If
From novice to tech pro — start learning today.