modules and passing variables

[ArielZusya]

I've recently discovered the joy of modules (as apposed to embedded VBA
associated with each form). I'm only just beginning to understand the full
richness of modules. For the most part I've discoverd that I can take some
of my more simple yet repetative code and place it in a module. For example,
Where I would before have a button that calls code that closes the current
form and opens a seperate form, I can place the code in a module and have the
name of the module be called from the form but with the name of the new form
passed to the module in parenthesis. So, if my CloseThisAndOpenThat function
takes stDocName as a variable in the module it would have

Public Sub CloseThisAndOpenThat (stDocName As String)

and then in the code for the button on the form it would have

CloseThisAndOpenThat ("frmSecondForm")

All of that works fantastically. Trouble is I'd like to do something
similar but passing more variables. If, in my module I have something like:

Public Sub CloseThisAndOpenThat (stDocName As String, _
stQueryText As String, lnOptSelection As Long)

and then try

CloseThisAndOpenThat("frmSecondForm", "where tblMain.Issue1 = 1", 2)

I get an "expected =" error. What am I doing wrong? Thanks for your help!

Ariel

 

[Klatuu]

The code for calling the function and the function declaration appear to be
correct. I suspect your error is happening in the function. Post the code
for the function and identify the line where the error occurs. Also, be sure
that your function is not named the same as the module name. Access doesn't
like that.

 

[George Nicholson]

"where tblMain.Issue1 = 1"

The problem could be anything, but the above looks pretty suspicious to me.
If you are passing that value directly to the Criteria argument of OpenForm
then there are 2 things wrong with it: it should not include WHERE or the
tblMain reference (i.e., I'm thinking it should just be "[Issue1] = 1").

Without seeing the full code of CloseThisAndOpenThat its impossible to tell.

 

[Stuart McCall]

I've recently discovered the joy of modules (as apposed to embedded VBA
associated with each form). I'm only just beginning to understand the
full
richness of modules. For the most part I've discoverd that I can take
some
of my more simple yet repetative code and place it in a module. For
example,
Where I would before have a button that calls code that closes the current
form and opens a seperate form, I can place the code in a module and have
the
name of the module be called from the form but with the name of the new
form
passed to the module in parenthesis. So, if my CloseThisAndOpenThat
function
takes stDocName as a variable in the module it would have

Public Sub CloseThisAndOpenThat (stDocName As String)

and then in the code for the button on the form it would have

CloseThisAndOpenThat ("frmSecondForm")

All of that works fantastically. Trouble is I'd like to do something
similar but passing more variables. If, in my module I have something
like:

Public Sub CloseThisAndOpenThat (stDocName As String, _
stQueryText As String, lnOptSelection As Long)

and then try

CloseThisAndOpenThat("frmSecondForm", "where tblMain.Issue1 = 1", 2)

I get an "expected =" error. What am I doing wrong? Thanks for your
help!

Ariel

Either remove the parens from your call:

CloseThisAndOpenThat "frmSecondForm", "where tblMain.Issue1 = 1", 2

or use the Call keyword:

Call CloseThisAndOpenThat("frmSecondForm", "where tblMain.Issue1 = 1", 2)


Incidentally, you don't need the parens here either:

CloseThisAndOpenThat ("frmSecondForm")

But the reason that code executed ok is because of the space character that
precedes the 1st paren.

So there are 3 calling methods for you. The choice is yours, whatever suits
your coding style. Just be sure to make your calls consistent, ie call all
sub procedures the same way. Less confusion for you and perhaps others
further down the road.

 

[ArielZusya]

Oh wow... I guess it really was that simple... so... why did it work without
the "Call" when it was just one variable being passed? is it just more
tollerant of my rookyisms? Thanks for the help!

 

[Stuart McCall]

Oh wow... I guess it really was that simple... so... why did it work
without
the "Call" when it was just one variable being passed? is it just more
tollerant of my rookyisms? Thanks for the help!

<SNIP>

From the help for 'Call Statement':

"You are not required to use the Call keyword when calling a procedure.
However, if you use the Call keyword to call a procedure that requires
arguments, argumentlist must be enclosed in parentheses. If you omit the
Call keyword, you also must omit the parentheses around argumentlist. If you
use either Call syntax to call any intrinsic or user-defined function, the
function's return value is discarded."

What that doesn't say is that, as I mentioned, t'was the space character wot
did it. Nothing to do with the number of arguments passed. To the VBA
compiler, the parens surrounding the argument(s) means 'evaluate this part
of the code line first'. In your case it was meaningless because you only
passed one argument (there was nothing else to evaluate), but the compiler
accepted it because it is valid syntax when it follows a space. In your
second example call you had no space, so the compiler interpreted it as an
expression. Since the result of an expression is most commonly assigned to a
variable, you got the 'expected =' error. Use of the Call keyword fixes this
because it instructs the compiler to read the code as an expression, but
'throws away' the result.

 

[ArielZusya]

Oh... OK... now I think I follow. Thanks for your help!