Passing a table name as a variable

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
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)
Question by:smm6809
  • 2
  • 2
LVL 36

Expert Comment

ID: 36999416
If it's just the name pass it as a string.

Author Comment

ID: 36999446
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"
LVL 36

Accepted Solution

Norie earned 2000 total points
ID: 36999477
Try this.

& " FROM " & t

That will include the value of t in the SQL not just 't'.
LVL 61

Expert Comment

ID: 36999520
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


Author Closing Comment

ID: 36999522
That works Thanks!

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question