sending access table object to public sub or function as argument

Posted on 2008-11-11
Last Modified: 2013-11-27
I am wanting to send one of the tables in my access database as an argument to a Public Function I have created that needs to open the table as a recordset.  I want to be able to send different tables to the function and have it perform the code using each table as the same variable.  I can't seem to figure out how I need to pass it to the function.  I have pasted the code that I am calling below.  I am wanting to call it like this FillQAblanks(tbl_GENQA) but this dosen't seem to work.

Public Function FillQAblanks()

Dim rsttblGENQA As ADODB.Recordset

Set rsttblGENQA = New ADODB.Recordset

Dim rstqryEmpdataGEN As ADODB.Recordset

Set rstqryEmpdataGEN = New ADODB.Recordset

Dim qryEmpdataGEN As String

Dim month1, month2, month3, month4, month5, month6 As Date

Dim myscore, mycount, myloops As Integer

rsttblGENQA.Open "tbl_GENQA", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

rstqryEmpdataGEN.Open "qry_EmpdataGEN", CurrentProject.Connection, adOpenKeyset, adLockOptimistic


Do While rsttblGENQA.EOF = False

    myaverage = 0

    mymonth = #8/15/2008#


        Select Case mymonth

            Case #8/15/2008#

                mymonth = #9/15/2008#

            Case #9/15/2008#

                mymonth = #10/15/2008#

            Case #10/15/2008#

                mymonth = #11/15/2008#

            Case #11/15/2008#

                mymonth = #12/15/2008#

            Case #12/15/2008#

                mymonth = #1/15/2009#

            Case #1/15/2009#

                mymonth = #2/15/2009#

        End Select

        If Date >= mymonth Then


            rstqryEmpdataGEN.Find "[Full Name] = '" & rsttblGENQA.Fields(0).Value & "'"

            If rstqryEmpdataGEN.Fields(4).Value <= mymonth - 180 Then

                If IsNull(rsttblGENQA.Fields(1).Value) Then

                    If myaverage = 0 Then

                        myscore = 0

                        mycount = 0

                        myloops = 1


                            If Not IsNull(rsttblGENQA.Fields(myloops).Value) And rsttblGENQA.Fields(myloops).Value <> 0 Then

                                myscore = myscore + rsttblGENQA.Fields(myloops).Value

                                mycount = mycount + 1

                            End If

                            myloops = myloops + 2

                        Loop Until myloops = 13

                        If mycount <> 0 Then

                            myaverage = myscore / mycount

                            rsttblGENQA.Fields(1).Value = myaverage



                            myaverage = Null

                        End If


                        rsttblGENQA.Fields(1).Value = myaverage


                    End If

                End If


                rsttblGENQA.Fields(1).Value = 0


            End If

        End If

    Loop Until mymonth = #2/15/2009#





End Function

Open in new window

Question by:redrp
    LVL 77

    Accepted Solution

    1. Public Function FillQAblanks(tname as string)

    10. rsttblGENQA.Open tname CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    You call it like..
    x = FillQABlanks("tbl_GENQA")

    Author Closing Comment

    Worked perfectly!  Thank you for keeping it simple and easy!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    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…

    734 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

    18 Experts available now in Live!

    Get 1:1 Help Now