HI,
i have a table into a form and i want to take the sum of a value, to another unbound textbox!
i create a button with this code to take the value but i have problem "error 2001" why?

code:
Me.textbox.Value = DSum("[Final_Price]", "[10_Buy]", "[JOIN_NUMBER]=" & Me.ID)

thanx

Part of the problem is that Access doesn't like to take values and make calculations while posting the results at the same time. Another problem is that you are using text concatenation in the DSum() function &.

If you post the sum you wish to make, and the data types in each field, we can post code that will work.

Something like:

Dim lPrice As Long
Dim lBuy As Long '?
Dim lSum As Long
...

I am not sure what you are trying to do with [Join_Number]=XXX.

lPrice = Me.FinalPrice
lBuy = Me.10_Buy
lSum = lPrice * lBuy

Me.textbox.Value = lSum

That will post it on your form, but as you know it will change as the values change. You can put this on many events to make it change, like on your button, on the On Current event of the form, on the Before Insert of Before Update events of the form, etc.

What do you do with this value after you create it?




mmcdonal

Look it up at: http://wrox.books24x7.com

Well, there is a form (1_****) and in this form there is a subform (10_Buy).

Me.textbox.Value = DSum("[Final_Price]", "[10_Buy]", "[JOIN_NUMBER]=" & Me.ID)

"join number" is the value of form "10_buy", and "ID" the value of the form "1_****".
So the way of join betwwen them is id and join number (actually is the same number to different forms)

now i want an unbound textbox of "1_****" to take the value of the sum of the field "Final Price".

If I understand this correctly, you want to sum all the values on the subform where the records are related to the main form.

If you have the sub form set up to only show related records, then you only need to create a sub form footer, and put the sum of the records in that footer. Use an unbound text box in the footer that shows =Sum([Final_Price]). Will that work for you?

You can also refer to that subform total on the main form using the method found in the Northwind Sample database Orders form. Open the database, open the Orders form, then change to design view. Then double click on the Orders subform, and look at the hidden footer and the sub total there. I think this is the method you want.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Yes , that's what i wanted thanx a lot!
i was so stupid i tried to take the value with different way!


whatever thanx a lot!

I would have done the same thing except I remembered the Northwind Orders form. I do a lot of brute force solutions when I can't come up with something more elegant like this. Stupid would have been to forget about it and say it couldn't be done. Now your office mates will think you are a genius! =)

mmcdonal

Look it up at: http://wrox.books24x7.com