Use Immediate window to execute a procedure

[CuriousMark]

I'm having trouble figuring out how to use the Immediate Window to execute a
procedure in my code. I have a book that tells me to simply enter the name of
the procedure in the immediate window and press enter, but that gives me an
error "Expected procedure, not variable". I also read an earlier post in
which the syntax is to type "Call" followed by the name of the procedure. I
get the same error. I have even duplicated the code exactly as written in my
book for testing a public function, entered "?" followed by the function
name. That gives me nothing - no error message, no action. I can't figure it
out. What am I doing wrong? Help please.

 

[Tom Wickerath]

Hi Mark,

Are you using any reserved words for things that you have assigned a name to
in Access? The reason I ask is that this KB article looks like it describes
your situation:

"Expected Function or Variable" Error Message
http://support.microsoft.com/kb/210134

Try downloading Access MVP Allen Browne's Database Issue Checker Utility.
Use it to check your database. It will flag the use of reserved words in
field and table names, but not in VBA code. For that, you'll need to scan the
list to make sure that you haven't used a forbidden word.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[CuriousMark]

Tom,

Thanks for your prompt reply, but that doesn't seem to explain it. I'm
pretty sure I'm not using any reserved words, and even if I create a brand
new database and only enter a simple procedure such as:

Public Sub Help()
Debug.Print "help"
End Sub

....I can't get it to work. If I type <Help> into the Immediate window, I get
"Expected procedure, not variable". If I type <?Help>, I get a blank line. If
I type <Call Help> I get the same "Expected procedure..." error.

What am I doing wrong?

Mark

 

[Allen Browne]

Mark, what version of Access is this?

If you are using Access 2007, you may find that no code works unless you add
your database folder to the trusted locations under:
Office Button | Access Options | Trust Center | Trust Center Settings

In any version, your code won't work if you have a problem with your library
references:
http://allenbrowne.com/ser-38.html

In what module did you put this code? If it is in the module of a form, it
won't work like that. Instead, create a new module (Modules tab of the
database window), and put the sub in there. You should then be able to press
Ctrl+G to open the Immediate window, and enter:
Call Help
to execute the procedure.

If you were actually trying to open the Microsoft Access help menu, this
works in Access 2007:
RunCommand acCmdMicrosoftAccessHelpTopics

There is nothing called just "Help" in the standard libraries, so it should
not clash with anything. (There is a hidden class named __help in the Access
12 library.)

 

[CuriousMark]

Thanks again, but this still didn't help.
I'm using Access 2003. I did have the code in a form module, so I tried it
as a standalone module and had the same problem. It works fine if I put it in
a procedure called by a form (e.g. Sub Button1_Click), so the code works, I
just can't get it to execute from the immediate window.

 

[CuriousMark]

OK Allen. I got it. Thanks very much.
When I put the procedure in a db level module, I made it private. When I
changed it to public, it worked.
Is there any way to execute form level procedures from the Immediate window
instead of having to go back to the form? That would really help in
constructing my code?

 

[Marshall Barton]

I'm having trouble figuring out how to use the Immediate Window to execute a
procedure in my code. I have a book that tells me to simply enter the name of
the procedure in the immediate window and press enter, but that gives me an
error "Expected procedure, not variable". I also read an earlier post in
which the syntax is to type "Call" followed by the name of the procedure. I
get the same error. I have even duplicated the code exactly as written in my
book for testing a public function, entered "?" followed by the function
name. That gives me nothing - no error message, no action. I can't figure it
out. What am I doing wrong? Help please.

If your procedure is a Function and you want to see result
value, then use:
?func(x)

If the procedure is a Sub (or a function where you want to
ignore the result), use
proc x
or
Call proc(x)

 

[CuriousMark]

Thanks Marshall. My problem was that I was trying exactly what you suggest
and it wasn't working. At Allen's suggestion I put the code in a db level
module rather than the form module, and it worked. But that makes it
difficult to write the code if I can't run the procedures in the form level
code from the immediate window. Can you suggest a way to do that?

Mark

 

[Allen Browne]

You can call the code in the form's module provided you explicitly make it
Public, and include the full module name when calling it.

For example, if you have this declaration in the module of Form1:
Public Function MyFunc()
then you can call it from the Immediate Window like this:
Call Form_Form1.MyFunc()

Note that the Form_ prefix is Access-speak for the name of that module. You
can see that Access uses that name if you look in the Title bar of the VBA
window when editing that module.

 

[Marshall Barton]

Public procedures in a class (including form) module are
methods of the class. This means that you need to qualify
the procedure with the class:

As long as the form that contains the procedure is open:
Forms!theform.proc x

If the procedure is independent of all values on the form,
then the form does not have to be open and it can be called
using:
Form_theform.proc x
But this is a special case so don't use this syntax unless
really have to.

 

[AccessVandal via AccessMonster.com]

Hi,
Here’s what I found in Help.

Help Method

Displays the Office Assistant and the built-in "What would you like to do?"
Assistant balloon for standard Office online Help.

Syntax

expression.Help

expression Required. An expression that returns an Assistant object.
It a reserve name in access, so just avoid using it.

I did try using your code, it had the same error, but once I change it to
Function instead of a Sub, it work. Than I when change it back to a Sub, it
works.

Close the DB without saving and open again than input your code.

This time a different error message, didn’t take down error code but
something like “Expect Sub or Variableâ€. On the third try, I did not have any
error messages.

Weird huh?