?
Solved

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

Posted on 2011-03-17
2
Medium Priority
?
243 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 2000 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
The best software application must always have an error handling tool
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

568 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