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 tblBudgetShareP roj.
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 frmBudgetShareP roj 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. rcdBudgetShareP roj.Year(I) or something.
Thanks so much for your help in advance.
Zoe
4 4846
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 32,573
Recognized Expert Moderator MVP
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 rcdBudgetShareP roj![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 :)
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 tblBudgetShareP roj, I could then perform on the previous record.
i.e.
Private Sub cmdUpdate_Click ()
Dim rcdBudgetShareP roj As DAO.RecordsetD
Dim Percent As Double
Dim I As Integer
Dim MaxID As Integer
Percent = 1 + Nz(frmPercent![Percent], 0)
Set rcdBudgetShareP roj = CurrentDb.OpenR ecordset("tblBu dgetShareProjec tions")
MaxID = Queries!qryMaxI D![MaxID]
With rcdBudgetShareP roj
For I = 1 To 3
Call .AddNew
![Year] = intYear + 1
![InflationEstima te] = 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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sue |
last post by:
On code-behind page:
(attributes set programatically for each of these elements)
linkbutton added to tablecell
textbox added to tablecell
tablecells added to tablerow
tablerow added to table (table.ID is TestTable)
On .aspx page:
<HeaderTemplate>
<asp:Table ID="TestTable" runat="Server" />
</HeaderTemplate>
|
by: Jamie Fryatt |
last post by:
Hi everyone, here's what id like to do.
I have a table with 2 fields, name and value
I need to be able to add multiple records quickly, for example I need to add
name value
abc 1
abc 2
abc 3
|
by: kie |
last post by:
hello,
i have a table that creates and deletes rows dynamically using
createElement, appendChild, removeChild.
when i have added the required amount of rows and input my data, i
would like to calculate the totals in each row.
when i try however, i receive the error:
"Error: 'elements' is null or not an object"
|
by: JDiamond |
last post by:
Hi,
I have a table called Hosts. The Hosts table contains the following
fields:
Each field represents a step in the project. The tech that completes
each step initials the respective columns using a combo box within a
form. These initials are stored in an engineer table in which the combo
|
by: RayPower |
last post by:
I'm having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4. The system is
client/server, multiusers based. The MDBs are using record locking.
Here is part of the code:
Dim wkSpace As Workspace, db As Database
Dim rstTrans As DAO.Recordset
Set wkSpace = DBEngine.Workspaces(0)
| |
by: rshivaraman |
last post by:
Hi :
I have a TableA with around 10 columns with varchar and numeric
datatypes
It has 500 million records and its size is 999999999 KB. i believe it
is kb
i got this data after running sp_spaceused on it. The index_size was
also pretty big in 6 digits.
On looking at the tableA
|
by: robinsiebler |
last post by:
I've never had any call to use floating point numbers and now that I
want to, I can't!
*** Python 2.5.1 (r251:54863, May 1 2007, 17:47:05) on win32. ***
0.29999999999999999
0.29999999999999999
|
by: karpalmera |
last post by:
Hello!
We are trying to delete 1,500 records from a parent table with two child tables
and it is taking hours to execute.
Here is the set-up.
- The parent table has 800,000 records. Child table A has 2.3 M records. Child table B has 200,000 records.
- The parent table has 5 indexes. Child table A has 6 indexes and Child table B has one index.
- Both child tables has contraints with 'on delete cascade' parameter.
|
by: sureshl |
last post by:
function cal()
{
var f = document.form1;
var regExp_Count = new RegExp("^+$");
f.price1.value = parseFloat(f.baseprice.value*(f.percen.value/100)).toFixed(0);
}
cal() functions , will calculate as such in that formula n display in the price1 text
using the text property onblur,
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |