Access 97: Function won't run from a macro

[Locoman]

Hi folks,

I have a tested and working function that works out what the drive and path of the db application is. It is placed in the "modules" section of my database.

When I try and "run" it with a RunCode command from my Autoexec macro (or any other) Access says it can't find the named fucntion.

Any help greatly appreciated.

Regards, Brian

 

[jebry]

Hi!

Make sure that you are using the key word Public:

Public Function MyFunction()

and note, that it does neen to be a function not a procedure.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[mp9]

Also, don't forget the brackets on the end of the Function Name parameter in the macro.

So if your function is called fctnTest and is defined as...

Public Function fctnTest()...

Then you need to call it as fctnTest() or =fctnTest() from the macro.

 

[Locoman]

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