Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 26th, 2008, 12:37 PM
Authorized User
Points: 250, Level: 4
Points: 250, Level: 4 Points: 250, Level: 4 Points: 250, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2006
Location: , , Greece.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default TAKE A VALUE

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


Reply With Quote
  #2 (permalink)  
Old September 29th, 2008, 01:26 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old September 30th, 2008, 06:39 AM
Authorized User
Points: 250, Level: 4
Points: 250, Level: 4 Points: 250, Level: 4 Points: 250, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2006
Location: , , Greece.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default


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".

Reply With Quote
  #4 (permalink)  
Old September 30th, 2008, 07:03 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old September 30th, 2008, 02:30 PM
Authorized User
Points: 250, Level: 4
Points: 250, Level: 4 Points: 250, Level: 4 Points: 250, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2006
Location: , , Greece.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!

Reply With Quote
  #6 (permalink)  
Old October 1st, 2008, 07:07 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off




All times are GMT -4. The time now is 06:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.