469,922 Members | 2,190 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,922 developers. It's quick & easy.

Adding New Table Records and calculating a Percentage increase


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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCalculate_Click()
  2. Dim dbBudget As DAO.Database
  3. Dim rcdBudgetShareProj As DAO.Recordset
  4. Dim Percent As Variant
  5. Dim I As Integer
  7. Set dbBudget = CurrentDb
  8. Set rcdBudgetShareProj = dbBudget.OpenRecordset("tblBudgetShareProj")
  9. Set Percent = frmPercent![Percent]
  11. For I = 1 To 3
  12. rcdBudgetShareProj.AddNew
  13. rcdBudgetShareProj![Year] = rcdBudgetShareProj![Year] + 1
  14. rcdBudgetShareProj![InflationEstimate] = Percent
  15. rcdBudgetShareProj![Budget/Estimate] = "Estimate"
  16. rcdBudgetShareProj![Statemented] = rcdBudgetShareProj![Statemented] * (Percent + 1)
  17. rcdBudgetShareProj.Update
  18. Next I
  20. 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.

Aug 3 '08 #1
4 4491
167 100+
Try seting Percent variable by referencing forms, like:
Expand|Select|Wrap|Line Numbers
  1. Set Percent = Forms!frmPercent![Percent]
And the loop can be:
Expand|Select|Wrap|Line Numbers
  1. i = 0
  2. Do Until i = 3
  3. rcdBudgetShareProj.AddNew
  4. rcdBudgetShareProj![Year] = rcdBudgetShareProj![Year] + 1
  5. rcdBudgetShareProj![InflationEstimate] = Percent
  6. rcdBudgetShareProj![Budget/Estimate] = "Estimate"
  7. rcdBudgetShareProj![Statemented] = rcdBudgetShareProj![Statemented] * (Percent + 1)
  8. rcdBudgetShareProj.Update
  9. i = i + 1
  10. Loop
Aug 7 '08 #2
32,232 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim Percent As Double
  2. 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCalculate_Click()
  2.   Dim rcdBudgetShareProj As DAO.Recordset
  3.   Dim Percent As Double
  4.   Dim I As Integer
  5.   Dim intYear As Integer
  6.   Dim curStatemented As Currency
  8.   'Handle the operator not entering a value for Percent better than this
  9.   '... unless no increase is what you want as a default
  10.   Percent = 1 + Nz(frmPercent![Percent], 0)
  11.   'Arrange to populate these or use a control on a form
  12.   intYear = ...
  13.   curStatemented = ...
  14.   Set rcdBudgetShareProj = CurrentDb.OpenRecordset("tblBudgetShareProj")
  16.   With rcdBudgetShareProj
  17.     For I = 1 To 3
  18.       Call .AddNew
  19.       ![Year] = intYear + 1
  20.       ![InflationEstimate] = Percent
  21.       ![Budget/Estimate] = "Estimate"
  22.       ![Statemented] = curStatemented * (Percent ^ I)
  23.       Call .Update
  24.     Next I
  25.   End With
  27. 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 :)
Aug 10 '08 #3
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.


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] = ![Statemented](MaxID) * (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.
Aug 19 '08 #4
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.


Aug 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Jamie Fryatt | last post: by
135 posts views Thread by robinsiebler | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.