• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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)
  • 2
  • 2
1 Solution
NorieVBA ExpertCommented:
If it's just the name pass it as a string.
smm6809Author Commented:
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"
NorieVBA ExpertCommented:
Try this.

& " FROM " & t

That will include the value of t in the SQL not just 't'.
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
' etc
End Function

smm6809Author Commented:
That works Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now