How to 'Call' with a dynamic argument

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Hi All,

I have several subroutines that build various tables
The subroutines have very similar names:

Sub CreateVersionInfoTable(strDBPath As String, strNewTableName As String)
Sub CreateExpensesTable(strDBPath As String, strNewTableName As String)
Sub CreateExpensesLookupTable(strDBPath As String, strNewTableName As
String)

In another form's code I want to loop through the names of tables and ensure
that the tables exist. If they do exist then I want to build the missing
table. When a table is missing (let's pretend it's the 'VersionInfo' table
then I want to

Call CreateVersionInfoTable(strDBPath As String, strNewTableName As String)

For every table that may be missing the syntax would be:

Call Create & 'MissingTable' & Table(strDBPath As String, strNewTableName As
String)

I built a string(strTableCreate) that is equal to
"CreateVersionInfoTable(strDBPath As String, strNewTableName As String)"

and then I had

Call strTableCreate and I get a Compile error 'Expected Sub, Function, or
Property'

How do I build Call arguments dynamically? OR is there some other
suggestion for this?
 
Karen,
Sounds like you want to create new functions on the fly.
What you want to change/create is a function name, not an
argument.
If's it's feasible, have 1 function, with the table name
as an argument (within the parantheses). Does that help?
Geof.
 
Hi Geof,

I'm not trying to create a function (or subroutine) on the fly, rather I'm
trying to create the line to call the appropriate subroutine on the fly. I
have several different different subroutines who all have an pathname and
tablename argument. The code in each subroutine is different depending on
which table may be involved because each table has different fields, keys
etc.

What I'm trying to do is create the line


Call CreateVersionInfoTable(strDBPath As String, strNewTableName As String)

Where the VersionInfo part of the string will be the name of the appropriate
table depending on the conditions tested in an If.....Then.....Else loop.

Does this make more sense?
 
I don't believe this is possible since Access needs to
compile the code, and any "Call" statement would have to
refer to a valid function name.
I don't quite follow why you can't use the standard
method as described by Geof. Or....
Would a Case statement work? If you weren't constantly
adding subroutines and there wasn't a whole lot of cases,
it should work.
Can you include the Call statement as part of your IF
statement?
If varSomething=Something Then
Call Function1()
Else
Call Function2()
End If

Other than that, I don't know what to say.
 
Thanks. I really wanted something that would work as my app grows so I
could use a Case statement and just update it.
 
Karen said:
Hi All,

I have several subroutines that build various tables
The subroutines have very similar names:

Sub CreateVersionInfoTable(strDBPath As String, strNewTableName As String)
Sub CreateExpensesTable(strDBPath As String, strNewTableName As String)
Sub CreateExpensesLookupTable(strDBPath As String, strNewTableName As
String)

In another form's code I want to loop through the names of tables and ensure
that the tables exist. If they do exist then I want to build the missing
table. When a table is missing (let's pretend it's the 'VersionInfo' table
then I want to

Call CreateVersionInfoTable(strDBPath As String, strNewTableName As String)

For every table that may be missing the syntax would be:

Call Create & 'MissingTable' & Table(strDBPath As String, strNewTableName As
String)

I built a string(strTableCreate) that is equal to
"CreateVersionInfoTable(strDBPath As String, strNewTableName As String)"

and then I had

Call strTableCreate and I get a Compile error 'Expected Sub, Function, or
Property'

How do I build Call arguments dynamically? OR is there some other
suggestion for this?
Click to expand...

You can use the "Eval" function to do exactly what you want. Whether it's
the best/most elegant solution to your requirement is a different matter!
 
I once stored VBA code in an Access table, exported from
the table, imported it to Excel and got Access to run the
Excel VBA procedure. I wonder if you could do the same in
Access? Create a module, import some code into it. Run
the code.
Geof.
 
Karen said:
I have several subroutines that build various tables
The subroutines have very similar names:

Sub CreateVersionInfoTable(strDBPath As String, strNewTableName As String)
Sub CreateExpensesTable(strDBPath As String, strNewTableName As String)
Sub CreateExpensesLookupTable(strDBPath As String, strNewTableName As
String)

In another form's code I want to loop through the names of tables and ensure
that the tables exist. If they do exist then I want to build the missing
table. When a table is missing (let's pretend it's the 'VersionInfo' table
then I want to

Call CreateVersionInfoTable(strDBPath As String, strNewTableName As String)

For every table that may be missing the syntax would be:

Call Create & 'MissingTable' & Table(strDBPath As String, strNewTableName As
String)

I built a string(strTableCreate) that is equal to
"CreateVersionInfoTable(strDBPath As String, strNewTableName As String)"

and then I had

Call strTableCreate and I get a Compile error 'Expected Sub, Function, or
Property'

How do I build Call arguments dynamically? OR is there some other
suggestion for this?
Click to expand...


The Select Case approach may be more managable, but there
are a couple of other things you can try.

If you make your procedures Functions, then the Eval
function will call a function in its string argument:

strTableCreate = "Create" & strMissingTable & "Table(""" _
& strDBPath & """, """ & strNewTableName & """)"
retval = Eval(strTableCreate)

Note that the expression service is not aware of VBA
variables, so you have to concatenate their values into the
expression.

Another feature that can be used with either Sub or Function
procedures is the Run method:

Application.Run "Create" & strMissingTable & "Table, _
strDBPath, strNewTableName
 
Marsh,

Thanks, I'll try this. I've never used the Eval function so that will be a
useful lesson. I think I tried Application.Run but I'll see..............
 
Geof,

I'm not trying to be difficult but I did want something that would be a bit
more Universal in my little app.. The Case statement would work, I just
wanted to build the string then run it.
 
The Eval Function could be used to Call different Functions.

It would probably be easier to create a Template database with your current
tabledefinitions in it and distribute that for every update of the back-end
and check through it's tables, fields, relations & indexes for anything
missing in the customers back-end (which is BTW what I do ;-))

ie Run DoCmd.TransferDatabase on new tables
loop through the tabledefs to find & add missing fields, indexes and relations

HTH

Pieter
 
Pieter,

Yep. It is time to grow up because this little app is starting to spread.
I'll work on that code
 
Marsh,

Eval worked perfectly, thanks so much

--
Karen
Karen said:
Marsh,

Thanks, I'll try this. I've never used the Eval function so that will be
a useful lesson. I think I tried Application.Run but I'll
see..............
Click to expand...
 
Back
Top