vba cant find procedure from ArcGis

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken - 08 Mar 2007 21:53 GMT

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

Reply to this Message

Ralph - 08 Mar 2007 22:19 GMT

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

> 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.
[quoted text clipped - 16 lines]
> Thanks in advance
> Ken

Reply to this Message

Ken - 09 Mar 2007 12:58 GMT

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

> Is ModulPassVar a Function? I tried it and it failed if it was a Sub, but
> worked as a Function.
[quoted text clipped - 19 lines]
> > Thanks in advance
> > Ken

Reply to this Message

Ralph - 09 Mar 2007 13:32 GMT

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

> 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.
[quoted text clipped - 33 lines]
> > > Thanks in advance
> > > Ken

Reply to this Message

Ken - 09 Mar 2007 14:28 GMT

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"

> 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
[quoted text clipped - 45 lines]
> > > > Thanks in advance
> > > > Ken

Reply to this Message

Ralph - 09 Mar 2007 15:43 GMT

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

> I can't tell you how much I appreciate your help, at least I'm moving again,
> but I get " illegal funtion call"
[quoted text clipped - 50 lines]
> > > > > Thanks in advance
> > > > > Ken

Reply to this Message

Ken - 09 Mar 2007 15:50 GMT

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.

> You are getting the error running the function? Or are you getting the error
> when you open the form and pass it the variable?
[quoted text clipped - 53 lines]
> > > > > > Thanks in advance
> > > > > > Ken

Reply to this Message

Ken - 09 Mar 2007 17:03 GMT

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.

> 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.
[quoted text clipped - 16 lines]
> Thanks in advance
> Ken

Reply to this Message