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

loop through a table of dates and pass to a procedure's VBA query

I have the attached which works, It loops through teh array and produces the data.  But actually I have a table with one column that are month-end dates for 10 years.  I need to actaylly loop through that table.  It is called tblMonthEndDates with only one column, MonthEndDate.  But I do not understand how to to this.  All of this is in a VBA module.
Public Sub JerryPassThrough()
'Simply goes to the server and pulls down the asset values which are then
'rounded up in the database process for reporting purposes.
On Error GoTo ErrorHandler
Dim strSelect As String
Dim qdfSelect As QueryDef
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstAccount As DAO.Recordset
Dim rstDestination As DAO.Recordset
Dim strAccount As String
Dim strReportDate As String
Dim strServer As String
Dim strDBName As String
Dim strLogin As String
Dim strPassword As String
Dim strCnn As String
Dim varDate As Variant

For Each varDate In Array(20040831, 20040930, 20041031, 20041130, 20041231, 20050131)

strServer = DLookup("Server", "tblServerDBData", "Keyname = 'JERRY' ")
strDBName = DLookup("DatabaseName", "tblServerDBData", "Keyname = 'JERRY' ")
strLogin = DLookup("Login", "tblServerDBData", "Keyname = 'JERRY' ")
strPassword = DLookup("Password", "tblServerDBData", "Keyname = 'JERRY' ")

strCnn = "ODBC;DSN=" & strServer & ";DB=" & strDBName & ";UID=" & strLogin & ";PWD=" & strPassword

If DoesObjectExist("qryPT_Jerry", "Query") Then DoCmd.DeleteObject acQuery, "qryPT_Jerry"

Set db = CurrentDb
Set qdfSelect = CurrentDb.CreateQueryDef("qryPT_Jerry")
qdfSelect.Connect = strCnn

qdfSelect.SQL = "EXEC p_us_crys_country_alloc_rpt @asof_dt = '" & varDate & "',@account_number = '601726' "

qdfSelect.ReturnsRecords = True
Set rst = qdfSelect.OpenRecordset()
Do Until rst.EOF
'Append the data from the returned recordset to the table
Set rstDestination = db.OpenRecordset("tblJerry", dbOpenTable)
With rstDestination
    ![asof_dt] = rst!asof_dt
    ![client_nm] = rst!client_nm
    ![fund_flag] = rst!fund_flag
    ![ppw_plan_id] = rst!ppw_plan_id
    ![ais_account_number] = rst!ais_account_number
    ![plan_nm] = rst!plan_nm
    ![account_tot_mkt_val] = rst!account_tot_mkt_val
    ![primary_currency_cd] = rst!primary_currency_cd
    ![country_type] = rst!country_type
    ![country_id] = rst!country_id
    ![country_nm] = rst!country_nm
    ![country_weight] = rst!country_weight
    ![country_mkt_val] = rst!country_mkt_val
    ![alt_currency_cd] = rst!alt_currency_cd
    ![alt_currency_fx_rate] = rst!alt_currency_fx_rate
    ![alt_currency_fx_date] = rst!alt_currency_fx_date
    ![alt_currency_mkt_val] = rst!alt_currency_mkt_val
    ![local_currency_cd] = rst!local_currency_cd
    ![local_currency_nm] = rst!local_currency_nm
    ![local_currency_fx_rate] = rst!local_currency_fx_rate
    ![local_currency_fx_date] = rst!local_currency_fx_date
    ![local_currency_mkt_val] = rst!local_currency_mkt_val
End With

Set qdfSelect = Nothing
Set rst = Nothing
Set rstDestination = Nothing
Set db = Nothing

DoCmd.DeleteObject acQuery, "qryPT_Jerry"

Next varDate

    Exit Sub
    If Err.Number = 3021 Then
        Resume Next
        MsgBox Err.Number & " Description: " & Err.Description & " Procedure:  PassThrough"
'        PassThrough = False
        Resume Exit_ErrorHandler
    End If

End Sub

Open in new window

1 Solution
Rey Obrero (Capricorn1)Commented:
you need to open the table tblMonthEndDates as recordset, btw, is this a local table?

dim rsDates as dao.recordset
set rsDates=db.openrecordset("tblMonthEndDates")

do until rsDates.eof    '< this will replace the Array part of your codes above

'your codes here



ssmith94015Author Commented:
I always forget that.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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