Solved

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

Posted on 2011-03-17
2
233 Views
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()
rst.MoveFirst
Do Until rst.EOF
'Append the data from the returned recordset to the table
Set rstDestination = db.OpenRecordset("tblJerry", dbOpenTable)
With rstDestination
    .AddNew
    ![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
    .Update
End With
rst.MoveNext
Loop

qdfSelect.Close
rst.Close
rstDestination.Close
db.Close
Set qdfSelect = Nothing
Set rst = Nothing
Set rstDestination = Nothing
Set db = Nothing

DoCmd.DeleteObject acQuery, "qryPT_Jerry"

Next varDate

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

End Sub

Open in new window

0
Comment
Question by:ssmith94015
2 Comments
 
LVL 120

Accepted Solution

by:
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


rsDates.movenext

loop

0
 

Author Closing Comment

by:ssmith94015
ID: 35160749
I always forget that.
0

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.

Question has a verified solution.

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

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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