Passing Arguments

A

alex

Passing Arguments

Hello,

I’ve created a sub to delete and open tables. It looks like this:
‘’’’’’’’’’’’’’’
Sub OpenCloseTable(strTableName As String)
DoCmd.Close acTable, strTableName
DoCmd.OpenTable (strTableName)
End Sub
‘’’’’’’’’’’’’’’’
With the code, however, I have to close and open the same table. I’d
like to do something like this:
‘’’’’’’’’’’’’’’‘
Sub OpenCloseTable(strOpenTable As String, strCloseTable as String)
DoCmd.Close acTable, strCloseTable
DoCmd.OpenTable (strOpenTable)
End Sub
‘’’’’’’’’’’’’’’’’
With the latter code, however, I may not always close and/or open a
table; i.e., I need to pass only one argument. Something like this:
‘’’’’’’’’’’’’’’’’
Call OpenCloseTable(“MyTable”,) ‘open table
Call OpenCloseTable(,”MyTable”) ‘close table
‘’’’’’’’’’’’’’’’’
Is this possible?
Thanks,
alex
 
J

Jon Lewis

Try this:

Sub OpenCloseTable(Optional strOpenTable As String, Optional strCloseTable
as String)
If Len(strOpenTable) > 0 Then
DoCmd.OpenTable (strOpenTable)
End If
If Len(strCloseTable) > 0 Then
DoCmd.Close acTable, strCloseTable
End If
End Sub

Passing Arguments

Hello,

I’ve created a sub to delete and open tables. It looks like this:
‘’’’’’’’’’’’’’’
Sub OpenCloseTable(strTableName As String)
DoCmd.Close acTable, strTableName
DoCmd.OpenTable (strTableName)
End Sub
‘’’’’’’’’’’’’’’’
With the code, however, I have to close and open the same table. I’d
like to do something like this:
‘’’’’’’’’’’’’’’‘
Sub OpenCloseTable(strOpenTable As String, strCloseTable as String)
DoCmd.Close acTable, strCloseTable
DoCmd.OpenTable (strOpenTable)
End Sub
‘’’’’’’’’’’’’’’’’
With the latter code, however, I may not always close and/or open a
table; i.e., I need to pass only one argument. Something like this:
‘’’’’’’’’’’’’’’’’
Call OpenCloseTable(“MyTable”,) ‘open table
Call OpenCloseTable(,”MyTable”) ‘close table
‘’’’’’’’’’’’’’’’’
Is this possible?
Thanks,
alex
 
A

alex

Try this:

Sub OpenCloseTable(Optional strOpenTable As String, Optional strCloseTable
as String)
If Len(strOpenTable) > 0 Then
    DoCmd.OpenTable (strOpenTable)
End If
If Len(strCloseTable) > 0 Then
    DoCmd.Close acTable, strCloseTable
End If
End Sub


Passing Arguments

Hello,

I ve created a sub to delete and open tables.  It looks like this:
Sub OpenCloseTable(strTableName As String)
DoCmd.Close acTable, strTableName
DoCmd.OpenTable (strTableName)
End Sub
With the code, however, I have to close and open the same table.  I d
like to do something like this:
Sub OpenCloseTable(strOpenTable As String, strCloseTable as String)
DoCmd.Close acTable, strCloseTable
DoCmd.OpenTable (strOpenTable)
End Sub
With the latter code, however, I may not always close and/or open a
table; i.e., I need to pass only one argument.  Something like this:
Call OpenCloseTable( MyTable ,) open table
Call OpenCloseTable(, MyTable ) close table
Is this possible?
Thanks,
alex
Click to expand...

Jon,
It works great, thanks.
Two quick things...I still have to pass a zero length string; e.g.,
call OpenCloseTable("myTable",""). I'm assuming that's by design (I
get an error otherwise).
Also, how could I open/close > 1 table; e.g., call
OpenCloseTable("myTable1" and "myTable2","")?
Thanks,
alex
 
J

Jon Lewis

I get no error (A2000 mdb and A2007 mdb & accdb) with either:
OpenCloseTable , "tableName" or OpenCloseTable "tableName" in both cases the
unspecified optional argument defaults to "".

For more than one table you need to specify delimited strings that you can
subsequently 'parse'.
e.g. comma delimited:
OpenCloseTable "table1,table2,table3"

Try Googling how to parse a delimited string for various techniques for
this.

HTH

Try this:

Sub OpenCloseTable(Optional strOpenTable As String, Optional strCloseTable
as String)
If Len(strOpenTable) > 0 Then
DoCmd.OpenTable (strOpenTable)
End If
If Len(strCloseTable) > 0 Then
DoCmd.Close acTable, strCloseTable
End If
End Sub


Passing Arguments

Hello,

I ve created a sub to delete and open tables. It looks like this:
Sub OpenCloseTable(strTableName As String)
DoCmd.Close acTable, strTableName
DoCmd.OpenTable (strTableName)
End Sub
With the code, however, I have to close and open the same table. I d
like to do something like this:
Sub OpenCloseTable(strOpenTable As String, strCloseTable as String)
DoCmd.Close acTable, strCloseTable
DoCmd.OpenTable (strOpenTable)
End Sub
With the latter code, however, I may not always close and/or open a
table; i.e., I need to pass only one argument. Something like this:
Call OpenCloseTable( MyTable ,) open table
Call OpenCloseTable(, MyTable ) close table
Is this possible?
Thanks,
alex
Click to expand...

Jon,
It works great, thanks.
Two quick things...I still have to pass a zero length string; e.g.,
call OpenCloseTable("myTable",""). I'm assuming that's by design (I
get an error otherwise).
Also, how could I open/close > 1 table; e.g., call
OpenCloseTable("myTable1" and "myTable2","")?
Thanks,
alex
 
S

Stefan Hoffmann

hi Alex,

Also, how could I open/close> 1 table; e.g., call
OpenCloseTable("myTable1" and "myTable2","")?
Click to expand...

Public Sub ParamArrayTest(ParamArray AParamArray() As Variant)

Dim value As Variant

For Each value In AParamArray()
If VarType(value) = vbString Then
Debug.Print value;
End If
Next value
Debug.Print

End Sub

In the immediate window:

ParamArrayTest "1", "2", "3", 4, "5"
1235!

The ParamArray data type must be Variant.


mfG
--> stefan <--
 
A

alex

hi Alex,



Public Sub ParamArrayTest(ParamArray AParamArray() As Variant)

   Dim value As Variant

   For Each value In AParamArray()
     If VarType(value) = vbString Then
       Debug.Print value;
     End If
   Next value
   Debug.Print

End Sub

In the immediate window:

ParamArrayTest "1", "2", "3", 4, "5"
1235!

The ParamArray data type must be Variant.

mfG
--> stefan <--
Click to expand...

Thanks for the help guys...
I'll give it a try.
alex
 
A

alex

Thanks for the help guys...
I'll give it a try.
alex- Hide quoted text -

- Show quoted text -
Click to expand...

Hi John,
I get no error (A2000 mdb and A2007 mdb & accdb) with either:
OpenCloseTable , "tableName" or OpenCloseTable "tableName" in both cases the
unspecified optional argument defaults to "".
Click to expand...

The reason I was getting the error is because I was calling the sub
like so:

Call OpenCloseTable "TableName"

Without 'Call' I don't get an error.

So...When are you supposed to use 'Call'? (I've always used it!) I
couldn't find anything definitive on the net.

Thanks,
alex
 
S

Stefan Hoffmann

hi Alex,

Not being John, neither Malkovich...

The reason I was getting the error is because I was calling the sub
like so:

Call OpenCloseTable "TableName"

Without 'Call' I don't get an error.

So...When are you supposed to use 'Call'? (I've always used it!) I
couldn't find anything definitive on the net.
Click to expand...
When using the Call syntax you need to enclose the parameters in
parentheses:

Call OpenCloseTable("TableName")


mfG
--> stefan <--
 
A

alex

hi Alex,

Not being John, neither Malkovich...

On 24.03.2010 12:12, alex wrote:> The reason I was getting the error is because I was calling the sub




When using the Call syntax you need to enclose the parameters in
parentheses:

  Call OpenCloseTable("TableName")

mfG
--> stefan <--
Click to expand...

Thanks Stefan; I'll take your advice anyday!
So there's no functionality difference, only that the Call syntax
requires parentheses (like a function).
alex
 
J

Jon Lewis

Call is normally used when 'calling' a function when you want to ignore the
result. Remember a Function normally returns a value whereas a Sub normally
just does something.

So instead of:

Dim Result As Whatever
Result = MyFunction

just

Call MyFunction

I belive that Call is unnecessary when applied to a Sub



hi Alex,

Not being John, neither Malkovich...

On 24.03.2010 12:12, alex wrote:> The reason I was getting the error is
because I was calling the sub




When using the Call syntax you need to enclose the parameters in
parentheses:

Call OpenCloseTable("TableName")

mfG
--> stefan <--
Click to expand...

Thanks Stefan; I'll take your advice anyday!
So there's no functionality difference, only that the Call syntax
requires parentheses (like a function).
alex
 
S

Stefan Hoffmann

hi Jon,

Call is normally used when 'calling' a function when you want to ignore the
result. Remember a Function normally returns a value whereas a Sub normally
just does something.
Click to expand...

http://msdn.microsoft.com/en-us/library/sxz296wz(VS.80).aspx

So the calling a function and discarding a value is the real use here.
I belive that Call is unnecessary when applied to a Sub
Click to expand...
It's a legacy from the days when BASIC wasn't visual. In some dialects
the Call was mandatory for subs.


mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Changing default in table field with tableDefs 7
Object Required compile error 1
Update table using data in a different .mdb 0
Dynamic Naming of Form Button 3
Help - onclick sub never runs 4
Adding table into existing access database via ADO 0

Top