Question Add up values from a table.

[DanJames]

I have a table with a column called "And" whitch shows the currency values like "£45" ect. I'd like to know how to add up all of the values in the column "And", and then put the currency value into a text box on a form onload.

So mabey something like

Private Sub Form_Load ()
Textbox2.Value = Sum("And" from "Table1")
End Sub

Or if it shouldnt be in Form_Load, tell me. So basically the textbox will always be the right value even if i changed a value while the form was open?

Thanks in Advance, Dan.

 

[jjturner]

If your form is bound, you can use the Control Source for the text box:
=Sum([And])
And put the text box in the Footer.

HTH,
John

 

[DanJames]

No, this doesn't work as the form is not bound. Mabey if you could show me how to make this form bound? Is there another way you can add these up (mabey VBA or a report???). Also I was thinking mabey smething like =Sum(AllClientDetails![And]) in the control source? Thanks alot, Dan.

 

[Kryst51]

Create a query that sums the column. Then base the row source for your text box off of the query.

 

[Kryst51]

And then in the control that would effect the total of the sum text box, in the after update event requery the sum text box

 

[DanJames]

Ok, I've made the query summing up the amounts. And then in control source I have put = [Quarterly Pay]![Quarterly]. But it doesn't show the value but says name! So what contol source for the text box shall i put? Thanks, Dan

 

[Kryst51]

You want to click on the drop down menu and find your query name. Click on it. Then you want to modify the column count, and column width to show onnly your sum. If you can post your db, I can do it for you, take some creen shots and post them so you can see.

 

[DanJames]

Wait, I don't have a drop down menu and don't want one. I just want for when the form loads the value of Text14 to say the value of Query1 For Example. As Query1 adds up all of the data and I need the value to go in the text box. Any Ideas? Thanks, Dan.

 

[Scooterbug]

Kryst is referring to the drop down entry on the Forms Property window. If you click on the ALL tab, the first property is called Record Source. Clicking on the drop down arrow there will bring up a list of all the tables and queries in your database. When you choose one, that form becomes bound to that object (table or query).

You can then display the field list, and all the fields from that table/query will be available as controls.

 

[DanJames]

Ahh right, can it be bound to two tables or queries?

 

[Scooterbug]

You can only have one record source...however you can create a query with as many tables or queries as you like. You can also use Subforms as well.

 

[DanJames]

Ok thanks mate, I used the Sub Form option and its working fine.