Passing a table name as a variable

Posted on 2011-10-20
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
    LVL 33

    Expert Comment

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

    Author Comment

    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 33

    Accepted Solution

    Try this.

    & " FROM " & t

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

    Expert Comment

    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

    That works Thanks!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now