Generate records using a query or VBA?

[DorenM]

Hello! Hope someone out here can get me going in the right direction. I'm trying to create journal entries based on the number of months between two dates, the beginning and end of a contract in the CONTRACTS table. I want to generate a record for each month a contract is active and post a dollar amount on each record in the Contracts_Detail
table. (MORE DETAIL: A contract active from 01/01 to 06/01 for $6,000 should generate entries in the ContractsDetail for field-value: Rec-1, Date-01/01, Amount-$1000; Rec-2, Date-02/01, Amount-$1,000; etc through Rec-6, Date-06/01, Amount-$1,000. The "Rec-x" values aren't important, but the other two field values are.)

Is this a VBA solution or an append query solution?

Thanks so much for your help!
D.

 

[papparuff]

Create a query or SQL statement to filter the dates you want. Then you want to create two recordsets. One for the query or SQL statement and one for the Details table. Something like this

Dim rs1 as dao.recordset
dim rs2 as dao.recordset
dim lngCost as long
dim intMonths as integer
dim datDate as Date
dim i as integer

set rs1=currentdb.openrecordset("YourQueryHere&quot
set rs2=currentdb.openrecordset ("tblContracts_Detail&quot

do while not rs1.eof
' Determine the number of months
' The StartDate, StopDate come from the query
intMonths = datediff("m",rs1!StartDate,rs1!StopDate)+1

' Determine the cost for each month
' The TotalCost comes from the query
lngCost=rs1!TotalCost/intMonths

datStartDate=rs1!StartDate
for i = 1 to intmonths

rs2.addnew
rs2!ContractID=rs1!ContractID
if i = 1 then
' The formula
' DateAdd("d", -(Day(datStartDate) - 1), datStartDate)
' gets day one of the datStartDate
rs2!RecDate=DateAdd("d", -(Day(datStartDate) - 1), datStartDate)

else

' Increment the startmonth by one month
datStartDate=dateadd("m",1,datStartDate)
rs2!RecDate=DateAdd("d", -(Day(datStartDate) - 1), datStartDate)

endif

rs2!MonthlyCost=lngCost
rs2.update
rs1.movenext
loop

rs2.close
rs1.close

set rs2=nothing
set rs1=nothing

This should work for you John Ruff - The Eternal Optimist

 

[DorenM]

Thanks so much! This got me going in the right direction in my new database.
D. :-D