Hi Jebry and mp9,
Well, I have the name as Public Function, and mp9 I tried entering an equals sign bbefore the name in the macro line, but this make no difference.
As it may be something odd in my code I include it below:
Option Compare Database
Option Explicit
Public MyDrive As String
Public Function FindLocation()
'This version requires a known special first character of the database filename to work outs its pathname
'MyDrive holds processed trimmed name string with just drive path
Dim MyLocation As Database ' gets temporary Database name (i.e. where this database is located)
' Access 97 can't use later direct calls so must use this clunky method!
Dim FullName As Variant ' var holds temp database name
Dim PathlenTrim As Variant ' holds difference of string length minus special chars " \# " in db filename
Dim NameLength As Variant
Dim StringChar As String
Dim StringHold As String
Dim i As Integer
'Find out where the database is located
'======================================
Set MyLocation = CurrentDb()
FullName = MyLocation.Name ' find the name
For i = 1 To Len(FullName)
StringChar = Mid$(FullName, i, 1)
If Asc(StringChar) <> 35 Then
StringHold = StringHold & StringChar 'continue building string
Else:
NameLength = i
End If
Next i
FullName = StringHold
StringHold = "" 'reset string buffer
PathlenTrim = NameLength - 2
MyDrive = Left(FullName, PathlenTrim) ' trim off the junk
Set MyLocation = Nothing
End Function