smm6809
asked on
Passing a table name as a variable
Is there a way to pass a table name to a function? I am passing the form name but would also like to pass the table name depending on what form I am calling the function from. For example:
pfGetData(byval f as form, t as table)
pfGetData(byval f as form, t as table)
If it's just the name pass it as a string.
ASKER
I guess it's a little more complicated then that because I want to use it in a SQL statement to open a recordset, so for example:
strSQL = "SELECT DISTINCT strXRef " _
& " FROM t "
Set rs = New ADODB.Recordset
rs.Open strSQL, mConn
So I get an error: cant find the table "t"
strSQL = "SELECT DISTINCT strXRef " _
& " FROM t "
Set rs = New ADODB.Recordset
rs.Open strSQL, mConn
So I get an error: cant find the table "t"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd pass both the form and the table names as strings, rather than passing them as objects.
Dim strFormName as string
Dim strTableName as string
strFormName = Me.Name
strTableName = "tblYourTable"
pfGetData strFormName, strTableName
' etc..
' The function would look something like this:
Function pfGetData(byval f as string, t as string)
' This is what your SQL code would look like (pointed out by imnorie):
strSQL = "SELECT DISTINCT strXRef " _
& " FROM " & t
' and this is an example of using a formname passed as a string
Dim ctl as Control
For Each ctl in Forms(f) loop... '<-- this is how you'd refer to a form based on it's name
'do something
next
' etc
End Function
Dim strFormName as string
Dim strTableName as string
strFormName = Me.Name
strTableName = "tblYourTable"
pfGetData strFormName, strTableName
' etc..
' The function would look something like this:
Function pfGetData(byval f as string, t as string)
' This is what your SQL code would look like (pointed out by imnorie):
strSQL = "SELECT DISTINCT strXRef " _
& " FROM " & t
' and this is an example of using a formname passed as a string
Dim ctl as Control
For Each ctl in Forms(f) loop... '<-- this is how you'd refer to a form based on it's name
'do something
next
' etc
End Function
ASKER
That works Thanks!