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

sending access table object to public sub or function as argument

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
 
rsttblGENQA.MoveFirst
Do While rsttblGENQA.EOF = False
    myaverage = 0
    mymonth = #8/15/2008#
    Do
        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.MoveFirst
            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
                        Do
                            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
                            rsttblGENQA.Update
                        Else
                            myaverage = Null
                        End If
                    Else
                        rsttblGENQA.Fields(1).Value = myaverage
                        rsttblGENQA.Update
                    End If
                End If
            Else
                rsttblGENQA.Fields(1).Value = 0
                rsttblGENQA.Update
            End If
        End If
    Loop Until mymonth = #2/15/2009#
 
    rsttblGENQA.MoveNext
Loop
rstqryEmpdataGEN.Close
rsttblGENQA.Close
End Function

Open in new window

0
redrp
Asked:
redrp
1 Solution
 
peter57rCommented:
1. Public Function FillQAblanks(tname as string)


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


You call it like..
x = FillQABlanks("tbl_GENQA")
0
 
redrpAuthor Commented:
Worked perfectly!  Thank you for keeping it simple and easy!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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