Using Formulas in Access same as Excel

[sanan]

Dear Sir/Madam

I am sort of new to Access, and I have few questions about Access, which could be very Easy to your experts.
These questions are related to each other, and are as follow:
Let’s say I have some data about the length and Width of some Rectangular, and I want to enter them in a table and also make some kind of form for my table.
Therefore we would end up with a Table Rectan And a Form Calrec.

Length Width Area
7 9 7*9
5 25 5*25
Table: Rectan

Now these are the question:
First of this table could growth to hundreds of Rows as we Enter more Data
1. My first question is, can I basically by writing some kind of codes treat my table just as If it was an Excel Spreadsheets? That is by entering the values for Length and Width columns in my table the Value of my Area Column would be calculated, which is the Multiplication of the other 2 values.
2. The second question is the same as first question but it applies to My Form.
I know that after creating my Form Calrec, I can write the following Code in the properties of Area > Data > Control Source: = [Length] * [Width]
And as I enter the new Values for Length and Width the Calculated values for area would appear in my area Space, Now my question is how can I capture or save the values of area from my form as I am entering the new values for the Length and width in My Table as well? I know It should be done by writing some kind of codes in Form properties > Area > event

I would like to thank you in Advance.

Best Regards
Sanan

 

[mph1]

As I understand your question, the variables Length and Width are known for each record which when multiplied together give the result Area.
To show the result Area, create an unbound text box and in the control source of this text box put =[Length]*[Width]. Since this can be calculated on the fly, it will not be necessary nore wise to store this value.


HTH


An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[Grumbledook]

Q1 = Default values do not recognise fields so...

Q2 = Use a hidden unbound text box for the calculation

I.E. Table consists of three fields:

Length/Width/Area

(if you want fractions/decimals set these fields to Number and field size to single)

On the form create the three bound text boxes.

Create a fourth text box and name it Calculate (for instance)

Set this data control to: = [Length] * [Width]

Then in the Width field set the After_Update property code to:
************************************************************
Private Sub wside_AfterUpdate()
Me.Area = Me.Calculate
End Sub
************************************************************ If you want the Area field to be locked then set the locked property to Yes and add this code instead:

************************************************************
Private Sub wside_AfterUpdate()
Me.Area.Locked = False
Me.Area = Me.Calculate
Me.Area.Locked = True
End Sub

************************************************************

This will automatically update the Area Field on the form and in the table

HTH

G.

 

[troix]

Where would I put this?

=[score1]+[score2]+[score3]+ etc etc


I need this to go into the field known as "Total"

 

[Grumbledook]

On the form:

Create 7 bound text boxes bound to the Score fields

Score1
Score2
Score3
Score4
Score5
Score6
TotalScore
Create an Hidden unbound text box and name it Calctotscore (for instance)and set Visible Property to No (Hidden)

Set the data control in this field to =[Score1] + [Score2] +[Score3] + [Score4] + [Score5] + [Score6]

Then in the [Score6] field set the After_Update property code to:
************************************************************
Private Sub Score6_AfterUpdate()
Me.TotalScore = Me.Calctotscore
End Sub
************************************************************ If you want the TotalScore field to be locked then set the locked property to Yes and add this code instead:

************************************************************
Private Sub wside_AfterUpdate()
Me.TotalScore.Locked = False
Me.TotalScore = Me.Calctotscore
Me.TotalScore.Locked = True
End Sub

************************************************************

This will automatically update the ToatalScore Field on the form and in the table

HTH

G.

 

[Grumbledook]

Private Sub wside_AfterUpdate()
Me.TotalScore.Locked = False
Me.TotalScore = Me.Calctotscore
Me.TotalScore.Locked = True
End Sub

Should read:

Private Sub Score6_AfterUpdate()
Me.TotalScore.Locked = False
Me.TotalScore = Me.Calctotscore
Me.TotalScore.Locked = True
End Sub

Sorry

G.

 

[sanan]

Dear Grumbledook
Thanks so much for your reply.
I go straight to my question, I did what I asked me to do, as matter of fact I even tried it with Coping and paste your codes, But It just does not work for me, I do not get any updates for the value of Area, in my either Table or my Forms I get the value of Area in my Text Box named calculate but I can not forward it to my Area Text Box and Consequently to my table cell for Area.
I also have few questions about your reply
1: What do you mean by Unbound and Bound Text Box? Could you explain a little?
2: In your Codes:

Private Sub wside_AfterUpdate()
Me.Area = Me.Calculate
End Sub

Where did you get the wside form or table from?

Finally I think the best is if you could just make an Access file for me for the above Example with 1 or 2 Rows of Data with the all the codes written in it and emailed it to via an attachment. I know it should not take you more than few minutes to write the Access file, and this could be much faster. I can write the access file my self with a Table and a form in it and Email it to you and you just Write the codes for me, just please provide your Email to me.

Best Regards
Sanan

 

[Grumbledook]

Sanan,

An bound text box is one bound to the data of a field from your table, an unbound box is not bound to any particular field. Text boxes can be hidden or visible by setting the visible property to yes or no.

Now as far as I can see you have a table called Rectan with three fields, [Length], [Width] and [Area] and you want the [Area] Field to be automatically updated and saved from your form Calrep. To achieve this follow these steps


On your form Calrep you have three text boxes bound to the following fields of your table Rectan: [Length],[Width]and [Area].

Now create a further text box - you will see this as an unbound text box. You can name this box anything you like but let us call it Calculate. Set the Control property of this box to: = [Length] * [Width]
This box will now do the calculation for the area of the rectangle.

At this point when you enter data nothing happens except that the calculate box updates to the area of the rectangle.

Now - to get this information into the [Area] field of the form create an event in the [Width] After_Update control
and type in the following code

Me.Area = Me.Calculate

The code should look like this:
************************************************************
Private Sub Width_AfterUpdate
Me.Area = Me.Calculate
End Sub
************************************************************

Now you will see the Area Box updating with the same information as the Calculate Box. However as you do not need to see the Calculations just set the visible property of the Calculate text box to no, which will hide it.

Now all you will see is when you enter the length then the width your Area will be automatically updated with the correct information.

But, you will need to 'lock ' the Area information so it cannot be changed inadvertantly, so set the locked property control on the Area box to yes. This will not allow you to update or change any information in the box.

Add the following two lines of code so the AfterUpdate event of the Width box reads:
************************************************************
Private Sub Width_AfterUpdate()
Me.Area.Locked = False
Me.Area = Me.Calculate
Me.Area.Locked = True
End Sub
************************************************************

These two lines of code will open the Area field for update and lock it after the information is stored.

I hope you understand this a little better, if not, let me know.

G.

 

[lespaul]

Why would you want to store a calculated field? This breaks third normal form (to begin with) and what if the user puts in 1000 new records one day and all the lengths that person entered are off by one. Someone has to manually update or write an update to recalculate all those fields.

Fundamentals of Relational Database Design: A table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are mutually independent. An obvious example of a dependency is a calculated column. For example, if a table contains the columns Quantity and PerItemCost, you could opt to calculate and store in that same table a TotalCost column (which would be equal to Quantity*PerItemCost), but this table wouldn't be 3NF. It's better to leave this column out of the table and make the calculation in a query or on a form or a report instead. This saves room in the database and avoids having to update TotalCost, every time Quantity or PerItemCost changes.

Leslie