WARNING: The following is all untest air-code.
1) Create a property on the Database and store the path there
To create a Database property, use code like:
Sub SetDatabaseTextProperty( _
PropertyName As String, _
PropertyValue As String _
)
On Error GoTo SetDatabaseTextProperty
DIm dbCurr As DAO.Database
Dim prpCurr As DAO.Property
Set dbCurr = CurrentDb()
dbCurr.Properties(PropertyName) = PropertyValue
End_SetDatabaseTextProperty:
Set prpNew = Nothing
Set dbCurr = Nothing
Exit Sub
Err_SetDatabaseTextProperty:
Select Case Err.Number
Case 3270
' Error 3270 means that the property was not found.
' (i.e.: it doesn't exist yet)
Set prpNew = dbCurr.CreateProperty( _
PropertyName, dbText, PropertyValue)
dbCurr.Properties.Append prpNew
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_SetDatabaseTextProperty
End Select
End Sub
To retrieve that property, you'd use CurrentDb.Properties("NameOfProperty")
(Note that an error 3270 will be raised if the property doesn't exist)
In other words, rather than having strFolderName declared as a global
variable, you'd have:
Function Select_folder()
Dim strFolder As String
strFolder = BrowseFolder("What Folder you want to select?")
Call SetDatabaseTextProperty("FolderName", strFolder)
End Function
Everyplace in your code that you're currently using strFolderName, use
CurrentDb.Properties("FolderName") instead.
2) Create a table to hold it
Presumably you know how to create a table. Create one called, say,
DatabaseProperties with two columns: PropertyName and PropertyValue (both
text fields). Instead of the SetDatabaseTextProperty sub I showed above,
you'd have
Sub SetDatabaseTextProperty( _
PropertyName As String, _
PropertyValue As String _
)
On Error GoTo SetDatabaseTextProperty
Dim strSQL As String
Dim varProperty As Variant
varProperty = DLookup(_
"PropertyValue", _
"DatabaseProperties", _
"PropertyName ='" & PropertyName")
If IsNull(varProperty) Then
strSQL = "INSERT INTO DatabaseProperties " & _
"(PropertyName, PropertyValue) " & _
"VALUES (" & Chr$(34) & PropertyName & Chr$34 & ", " & _
Chr$(34) & PropertyValue & Chr$(34) & ")"
Else
strSQL = "UPDATE DatabaseProperties " & _
"SET PropertyValue = " & Chr$(34) & PropertyValue & Chr$(34) & _
" WHERE PropertyName = " & Chr$(34) & PropertyName & Chr$(34)
End If
CurrentDb.Execute strSQL, dbFailOnEreror
End_SetDatabaseTextProperty:
Exit Sub
Err_SetDatabaseTextProperty:
MsgBox Err.Number & ": " & Err.Description
Resume End_SetDatabaseTextProperty
End Sub
To retrieve that property, you'd use the DLookup statement in the sub above.
(Note that DLookup will return Null if the property doesn't exist)
In other words, rather than having strFolderName declared as a global
variable, you'd have:
Function Select_folder()
Dim strFolder As String
strFolder = BrowseFolder("What Folder you want to select?")
Call SetDatabaseTextProperty("FolderName", strFolder)
End Function
Everyplace in your code that you're currently using strFolderName, use
DLookup("PropertyValue", "DatabaseProperties", "PropertyValue =
'FolderName'") instead.
Post back if you want details on options 3 or 4.
