Do Next Loops to Create Recordsets??? (1 Viewer)

[Chanda]

Hi All,
I've been working with Access for a while, but am a begginer when it comes to VBA. I have a Contract database, in the ContractDetail Table there is a Contract Start Date and a Contract End Date. In a query based on the table I have two calculated fields: Months (DateDiff between Start and End Date), and Monthly Payment. I need to somehow get a returned recordset that would give me a row for each month between the Start Date and the End Date of the contract and the monthly payment. For Example: Start Date = 04/01/00 End Date = 08/01/00 Monthly Payment = $425
Months = 4
I need to have a recordset that looks like this:
Date Monthly Payment
04/01/00 $425
05/0100 $425
06/01/00 $425
07/01/00 $425
I'm thinking some kind of Do Next Loop that would take the Start Date and add 1 month each time it went through the loop, the number of times it would go through the loop would be based on the number in the "Months" field. The number of months added to the Start Date would also need to be incremented by one for each time it went through the loop. The first time it adds 1 month, the second time 2 months, ect.... I'm thinking this would be stored in a temporary table.
The problem is, I have no idea if this is even possible the way I'm thinking about it!
Can you create recordset like this? Or, is there some really simple way to do this that I'm just overlooking? I'd really, really appreciate any help on where to start with this problem.
Thanks in Advance!
Chanda

 

[StephenS]

Chanda,

Here is some code that I put together. It is not tested or debugged yet. But it is my approach to your question. It may not precisely fit what you have, but I think you will be able to use some ideas from it. Be sure you have all of the necessary references checked in VBA. Otherwise, some of the recordset statements may not work.

Private Sub Command0_Click()
' I have two tables, Table1 and Table2
' - Table1 has DateDiff - Integer; MonthlyPayment - Integer
' - Table2 has ThisDate - Integer; ThisPayment - Integer
' The first table is the table you mentioned with the two values.
' The second table is where we will define the recordset and put
' your new values (you didn't say exactly what you wanted to do
' with them.

' We need to 1. Get values from your table into a recordset.
' 2. Add to the date and put them into a recordset.

' I am assuming in this that there is only one record in the first
' table. If not, you will have to adjust the first part of this.

' First, Dim some values
Dim rstGetInfo As Recordset
Dim rstNewInfo As Recordset
Dim datFirstDate As Date, datNewDate As Date
Dim dbs As Database
Dim intNumMonths As Integer, intAmountPerMonth As Integer, I As Integer
Dim strSQL As String

' Initialize

datFirstDate = #4/1/2000# ' You didn't say where your first date came from so
' I am hardcoding this
Set dbs = CurrentDb

' Get Some Values from the first table

Set rstGetInfo = dbs.OpenRecordset("Table1")
intNumMonths = rstGetInfo(0) ' 0 refers to the first field in the table
intAmountPerMonth = rstGetInfo(1) ' 1 refers to the second field in the table

' Access also lets you use the name of the field rather than a number

' Now, set up the second recordset to produce some new dates with amounts:
Set rstNewInfo = dbs.OpenRecordset("Table2")
datNewDate = datFirstDate

With rstNewInfo
.MoveLast ' MoveLast and MoveFirst populates the recordset (never hurts to
' do this - though this should be an empty recordset)
.MoveFirst ' Now we are on the first record of an empty recorset
For I = 1 To intNumMonths
rstNewInfo(0) = datNewDate ' put in first field [ThisDate]
rstNewInfo(1) = intAmountPerMonth ' put in second field [ThisPayment]
datNewDate = DateAdd("m", 1, datNewDate)
' The DateAdd Function will add one month for you
.MoveNext
Next I
End With

End Sub

Hope that Helps!

Stephen Smith