From: alex on
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
From: Jon Lewis on
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

"alex" <sql_aid(a)yahoo.com> wrote in message
news:303e9ea1-534b-46e6-8d2e-3a2e6d8f8a83(a)v20g2000yqv.googlegroups.com...
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


From: alex on
On Mar 18, 12:02 pm, "Jon Lewis" <jon.le...(a)cutthespambtinternet.com>
wrote:
> 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
>
> "alex" <sql_...(a)yahoo.com> wrote in message
>
> news:303e9ea1-534b-46e6-8d2e-3a2e6d8f8a83(a)v20g2000yqv.googlegroups.com...
> 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

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
From: Jon Lewis on
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

"alex" <sql_aid(a)yahoo.com> wrote in message
news:fa3a20df-1cf4-4844-a8b7-ffb254be33f7(a)g26g2000yqn.googlegroups.com...
On Mar 18, 12:02 pm, "Jon Lewis" <jon.le...(a)cutthespambtinternet.com>
wrote:
> 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
>
> "alex" <sql_...(a)yahoo.com> wrote in message
>
> news:303e9ea1-534b-46e6-8d2e-3a2e6d8f8a83(a)v20g2000yqv.googlegroups.com...
> 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

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


From: Stefan Hoffmann on
hi Alex,

On 18.03.2010 19:26, alex wrote:
> Also, how could I open/close> 1 table; e.g., call
> OpenCloseTable("myTable1" and "myTable2","")?

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 <--