vba cant find procedure from ArcGis

K

Ken

I have this question posted on the ESRI forums as well but with not much help
yet so I thought I would look for input here as well.
post at ESRI:
http://forums.esri.com/Thread.asp?c=93&f=992&t=215526&mc=0

From ArcGIS I call Access and try to set a public variable for a form to
check when it opens. This is the code:

'Open Access and pass the where clause to a public variable

AccessApp.OpenCurrentDatabase "C:\traffic\traffic.mdb", False
AccessApp.Visible = True
'AccessApp.DoCmd.OpenForm "ordersunbound", acNormal, , , , acWindowNormal
AccessApp.Run "ModulPassVar", sOut
I get the error in ArcGIS that it can't find the procedure 'ModulPassVar'

If anyone has any suggestions I would greatly appreciate it,
I need to pass this variable to access at this point I don't care how.
Thanks in advance
Ken
 
R

Ralph

Is ModulPassVar a Function? I tried it and it failed if it was a Sub, but
worked as a Function.
 
K

Ken

This is my function in Access, and I'm still getting the error. I tried
changing it to a public sub and still didn't work.

Public Function ModulPassVar(sOut As String)
Public StrWhere As String
If sOut <> "" Then
StrWhere = sOut
Else
StrWhere = ""
End If
Debug.Print sOut

End Function
 
R

Ralph

I copied your function and used it in Excel, it failed because of the Public
StrWhere, it gave me an Invalid attribute in sub or function, but when I
changed Public to Dim it worked. Below is my code from Excel.

Dim a As New Access.Application
Dim sOut As String
sOut = "Ralph"
a.OpenCurrentDatabase (ThisWorkbook.Path & "\val.mdb")
a.Visible = True
a.Run "ModulPassVar", sOut
 
K

Ken

I can't tell you how much I appreciate your help, at least I'm moving again,
but I get " illegal funtion call"
I've tried several different setups and now I keep getting "illegal funtion
call"
 
R

Ralph

You are getting the error running the function? Or are you getting the error
when you open the form and pass it the variable?
 
K

Ken

Opening the form is a remark, I had it in the code, from a previous use. I
am just trying to run the Function. I was able to call the funtion from in
Access without a problem.
 
K

Ken

Got it!
I was passing ModulPassVar, sOut
to the module in access:
function ModulPassVar(sOut as string)
I was picturing receiving "sOut", but I was receiving a string so I had to
change the variable in the declaration part. I was passing to the same
variable. (near as I can figure)
sorry for the trouble and Thanks loads for the help.
 
Top