B
Brandon Cheal
Hi, I have a main form with a date field (forecast date).
A user picks a month that savings will begin. The field
actually stores an integer and increases by a value of 1
for each month. Dec-2004 = 20; Jan-2005 = 21; Feb-2005 =
22 and so on...
So the user initially picks a month and types in a number
from 1 to 12 to count how many months savings will occur.
He/she then hits calculate. I take two separate savings
numbers (Plan Savings and Forecast Savings), divide each
by the number of months and enter that value for the
respective savings amounts to the corresponding months.
The subform is linked by ProjectID. Here is the code. It
works great.
With Me!sfrmProjectBreakout.Form.RecordsetClone
For x = 1 To intSavingsLength
'add savings records to the subform
.AddNew
!ProjectID = intProjectNo
!SavingsMonth = intPlanDate
'intPlanDate is the value of the Forecast Date field on
the main form
!TotalPlanSavings = curPlanSavingsAmt
!TotalForecastSavings =
curForecastSavingsAmt
.Update
intPlanDate = intPlanDate + 1
Next x
End With
End Product looks like this for 6 month savings:
I have a third field they will make entries in called
Actual Savings as well that the user will update as the
project moves.
Month Plan Save Forecast Save Actual Save
Jan-05 100 100 0
Feb-05 100 100 0
Mar-05 100 100 0
Apr-05 100 100 0
May-05 100 100 0
What I want to do now is allow the user to change the
forecast date on the main form and automatically cascade
that change to the subform. Here is where I struggle. I
do not want to delete any records. I have to keep the
records because the plan savings for each month will never
change, we want to track our progress against plan. I do
want to zero out the forecast savings and the actual
savings fields for all records that have a month value
earlier than the updated forecast value on the main form.
In plain English, when the user changes the forecast date
on the main form from January 2005 to March 2005, I want
to automatically update the subform records to reflect
this change. See new values.
Month Plan Save Forecast Save Actual Save
Jan-05 100 0 0
Feb-05 100 0 0
Mar-05 100 100 0
Apr-05 100 100 0
May-05 100 100 0
I am not sure how to change these values through code in
the after update event. I have come up with something
like this (see code below) but I'm not sure how to
properly use the Seek event.
'Set the forecast date to be the updated value from the
combo box.
intForecastDate = Me.ForecastDate.Value
With Me!sfrmProjectBreakout.Form.RecordsetClone
.Index = "SavingsMonth"
For x = 1 To 20
varBookmark = .Bookmark
.Seek "<", intForecastDate
!TotalActualSavings = 0
!TotalForecastSavings = 0
.Update
x = x + 1
If .NoMatch Then
.Bookmark = varBookmark
Exit Sub
End If
Next
End With
I'm not sure how to only run the code if the seek finds
something, so I used a For statement with x going up to 20
records. Please help!!!!!!
A user picks a month that savings will begin. The field
actually stores an integer and increases by a value of 1
for each month. Dec-2004 = 20; Jan-2005 = 21; Feb-2005 =
22 and so on...
So the user initially picks a month and types in a number
from 1 to 12 to count how many months savings will occur.
He/she then hits calculate. I take two separate savings
numbers (Plan Savings and Forecast Savings), divide each
by the number of months and enter that value for the
respective savings amounts to the corresponding months.
The subform is linked by ProjectID. Here is the code. It
works great.
With Me!sfrmProjectBreakout.Form.RecordsetClone
For x = 1 To intSavingsLength
'add savings records to the subform
.AddNew
!ProjectID = intProjectNo
!SavingsMonth = intPlanDate
'intPlanDate is the value of the Forecast Date field on
the main form
!TotalPlanSavings = curPlanSavingsAmt
!TotalForecastSavings =
curForecastSavingsAmt
.Update
intPlanDate = intPlanDate + 1
Next x
End With
End Product looks like this for 6 month savings:
I have a third field they will make entries in called
Actual Savings as well that the user will update as the
project moves.
Month Plan Save Forecast Save Actual Save
Jan-05 100 100 0
Feb-05 100 100 0
Mar-05 100 100 0
Apr-05 100 100 0
May-05 100 100 0
What I want to do now is allow the user to change the
forecast date on the main form and automatically cascade
that change to the subform. Here is where I struggle. I
do not want to delete any records. I have to keep the
records because the plan savings for each month will never
change, we want to track our progress against plan. I do
want to zero out the forecast savings and the actual
savings fields for all records that have a month value
earlier than the updated forecast value on the main form.
In plain English, when the user changes the forecast date
on the main form from January 2005 to March 2005, I want
to automatically update the subform records to reflect
this change. See new values.
Month Plan Save Forecast Save Actual Save
Jan-05 100 0 0
Feb-05 100 0 0
Mar-05 100 100 0
Apr-05 100 100 0
May-05 100 100 0
I am not sure how to change these values through code in
the after update event. I have come up with something
like this (see code below) but I'm not sure how to
properly use the Seek event.
'Set the forecast date to be the updated value from the
combo box.
intForecastDate = Me.ForecastDate.Value
With Me!sfrmProjectBreakout.Form.RecordsetClone
.Index = "SavingsMonth"
For x = 1 To 20
varBookmark = .Bookmark
.Seek "<", intForecastDate
!TotalActualSavings = 0
!TotalForecastSavings = 0
.Update
x = x + 1
If .NoMatch Then
.Bookmark = varBookmark
Exit Sub
End If
Next
End With
I'm not sure how to only run the code if the seek finds
something, so I used a For statement with x going up to 20
records. Please help!!!!!!