Solved

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

Posted on 2011-03-17
2
230 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 119

Accepted Solution

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

757 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

21 Experts available now in Live!

Get 1:1 Help Now