Adding New Table Records and calculating a Percentage increase

[zoeb]

Hi.

I have a form which the user enters 2 years worth of data into (one record per year). The aim, is to populate the table this form is based on with 3 more years worth of data (i.e. creating 3 new records), based on a percentage increase on the previous year. This form is based directly on a table called tblBudgetShareProj.

So far I have the following code but I am COMPLETELY new to VB and I'm very aware of how incomplete it is. I have a few ideas but no idea how to implement them.

So far when frmBudgetShareProj closes, it opens another form called frmPercent. In this form is an unbound text box called "Percent" which I would like the user to enter the percentage increase in, which will remain constant for the next 3 years. There are then 2 buttons, one to close (which works) and one called cmdCalculate, which on press, should add 3 new records and calculate the percentage increase for each record based on the previous.

I thought that perhaps selecting maximum user ID and then performing calculations based on the data for the fields associated with that ID would enable me to do the calculation based on the previous record. But then I didn't know how to control only doing this 3 times.

Any help would be GREATLY appreciated.

Private Sub cmdCalculate_Click()
Dim dbBudget As DAO.Database
Dim rcdBudgetShareProj As DAO.Recordset
Dim Percent As Variant
Dim I As Integer
 
Set dbBudget = CurrentDb
Set rcdBudgetShareProj = dbBudget.OpenRecordset("tblBudgetShareProj")
Set Percent = frmPercent![Percent]
 
For I = 1 To 3
rcdBudgetShareProj.AddNew
rcdBudgetShareProj![Year] = rcdBudgetShareProj![Year] + 1
rcdBudgetShareProj![InflationEstimate] = Percent
rcdBudgetShareProj![Budget/Estimate] = "Estimate"
rcdBudgetShareProj![Statemented] = rcdBudgetShareProj![Statemented] * (Percent + 1)
rcdBudgetShareProj.Update
Next I
 
End Sub

Currently, setting Percent says the value is missing so I think I've reference that unbound text box wrong.

And I know the for loop isn't stepping through anything but I didn't know how to incorporate the I value into the fields i.e. rcdBudgetShareProj.Year(I) or something.

Thanks so much for your help in advance.

Zoe

[hjozinovic]

Try seting Percent variable by referencing forms, like:

Set Percent = Forms!frmPercent![Percent]

And the loop can be:

i = 0
Do Until i = 3
rcdBudgetShareProj.AddNew
rcdBudgetShareProj![Year] = rcdBudgetShareProj![Year] + 1
rcdBudgetShareProj![InflationEstimate] = Percent
rcdBudgetShareProj![Budget/Estimate] = "Estimate"
rcdBudgetShareProj![Statemented] = rcdBudgetShareProj![Statemented] * (Percent + 1)
rcdBudgetShareProj.Update
i = i + 1
Loop

[NeoPa]

I suspect the Percent problem is related to trying to treat the value as an object.

VBA is very forgiving about how you define variables, which often means that errors are easier to introduce. I suspect you really want Percent to be a Double (double precision) variable. It should then be set, in case of an empty value, using Nz(). To simplify later calculations though, we also add 1 at this time.

Dim Percent As Double
Percent = Nz(frmPercent.Percent, 0)

Your Year code cannot use rcdBudgetShareProj![Year], as that is indeterminate after moving away from the current record (part of your processing). This must be available somewhere from elsewhere than here - either on a form or in your code. The same is true for the [Statemented] value.

You're right to use the For Next construct for your loop. This is the most appropriate of the looping constructs to use. I would however suggest using With for your main object (The recordset).

As near as possible, your code should be like the following :

Private Sub cmdCalculate_Click()
  Dim rcdBudgetShareProj As DAO.Recordset
  Dim Percent As Double
  Dim I As Integer
  Dim intYear As Integer
  Dim curStatemented As Currency
 
  'Handle the operator not entering a value for Percent better than this
  '... unless no increase is what you want as a default
  Percent = 1 + Nz(frmPercent![Percent], 0)
  'Arrange to populate these or use a control on a form
  intYear = ...
  curStatemented = ...
  Set rcdBudgetShareProj = CurrentDb.OpenRecordset("tblBudgetShareProj")
 
  With rcdBudgetShareProj
    For I = 1 To 3
      Call .AddNew
      ![Year] = intYear + 1
      ![InflationEstimate] = Percent
      ![Budget/Estimate] = "Estimate"
      ![Statemented] = curStatemented * (Percent ^ I)
      Call .Update
    Next I
  End With
 
End Sub

From your original code I've guessed that the value you are expecting for a value of 27 percent would be 27 percent itself (or 0.27). My code works on this assumption.

Let us know how you get on :)

[zoeb]

Hi, thanks so much for all your help, it's so frustrating when you don't quite know enough to get to the final solution.

Just one more quick question,

I was looking to update on the previous record, so I thought by having a query finding the maximum ID in the tblBudgetShareProj, I could then perform on the previous record.

i.e.

Private Sub cmdUpdate_Click()
Dim rcdBudgetShareProj As DAO.RecordsetD
Dim Percent As Double
Dim I As Integer
Dim MaxID As Integer

Percent = 1 + Nz(frmPercent![Percent], 0)

Set rcdBudgetShareProj = CurrentDb.OpenRecordset("tblBudgetShareProjections ")

MaxID = Queries!qryMaxID![MaxID]

With rcdBudgetShareProj
For I = 1 To 3
Call .AddNew
![Year] = intYear + 1
![InflationEstimate] = Percent
![Budget/Estimate] = "Estimate"
![Statemented] =  * (Percent ^ I)
Call .Update
Next I
End With

End Sub

I don't know if my syntax is correct but I hope you get what I'm trying to explain.

Thanks once again and sorry for the very late response.

[zoeb]

P.S. Having thought about this, doing it this way, should MaxID actually be MaxID -1, because when I add a new record, the ID count will increase and to query on the previous record I would need to refer to one less than the item calculated in the query.

Thanks,

Zoe