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

- 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

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