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

Posted on 2011-03-17
Last Modified: 2012-05-11
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

Question by:ssmith94015
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 35160349
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




Author Closing Comment

ID: 35160749
I always forget that.

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

773 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