Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-11-2006, 05:55 PM   #1
dblaszak
Registered User
 
Join Date: Oct 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
dblaszak is on a distinguished road
Question Auto Updating Total Order Cost After Quantity is changed

Ok so here is my problem:
I have a form that is realted to four differnt tables with the names: CUSTOMER, ORDER, PRODUCT AND ORDER LINE. With-in the table Order-Line there is a entity called, line_item_cost whcih is the total amount of the order with respect to Quanity_Ordered which comes from the ORDER LINE table and Price which comes from the PRODUCT table and Discount (%) which comes from the CUSTOMER table. When the users goes to change the Quantity_Ordered I need the form to automaticly update the line_item_cost which would calculate [Price]*[Quantity_Ordred]-[Price]*[Discount] . I tried to build a code in VBA to do this, Here is the Code:
Dim a, b, c, d
a = Me.[Quantity_Ordered]
b = Me.[Price]
c = Me.[Discount]
d = a * b - b* c
Me.[Line_Item_Cost] = d

VBA Complies the program but it does not show up in my form.
I think I might be updating it at the wrong point.
Any Suggestions.????

dblaszak is offline   Reply With Quote
Old 10-12-2006, 12:12 AM   #2
rborob
Registered User
 
Join Date: Jun 2006
Posts: 116
Thanks: 0
Thanked 3 Times in 1 Post
rborob is an unknown quantity at this point
thats very messy

Dim a, b, c, d as double
a = Me.[Quantity_Ordered]
b = Me.[Price]
c = Me.[Discount]
d = ((a*b) - (b*c))
Me.[Line_Item_Cost] = d

tidier. not sure where the problem is though...can you post the database to look at?
rborob is offline   Reply With Quote
Old 10-12-2006, 12:26 AM   #3
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
You should not be storing this value. You can calculate this in a query (your form should be based on queries, not tables, for this and other reasons) or in your form. VBA is not needed.

__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 10-12-2006, 01:20 AM   #4
ansentry
Access amateur
 
ansentry's Avatar
 
Join Date: May 2003
Location: Melbourne, Australia
Posts: 995
Thanks: 0
Thanked 7 Times in 7 Posts
ansentry is on a distinguished road
Send a message via Skype™ to ansentry
What was wrong with this link?

I gave you an answer, which you did not acknowledge as unhelpful, so I assumed it solved your problem.

You have reposted here and created another thread, not only that but you have double posted on another forum.
__________________
Regards,



John A
ansentry is offline   Reply With Quote
Old 10-12-2006, 04:50 AM   #5
dblaszak
Registered User
 
Join Date: Oct 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
dblaszak is on a distinguished road
Problem With The Query

I tried to do this from the example that you gave me, but I canít get it to work because my information comes from 4 different tables and I just could not get them to link in the query? Is there anything I can do about that? Sorry I forgot to put down that it was helpful, it was very helpful and I thank you all.
Thanks
dBlaszak

dblaszak is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Update date when another field is changed jamphan Queries 2 12-28-2004 11:43 AM
Compact/Repair DB Util not updating auto numbers smjohns Tables 6 03-16-2004 03:02 AM
purchase order auto number lines Ade F General 11 03-03-2004 12:55 AM
Auto input new record or changed record date cliff7376 Forms 2 09-26-2003 05:38 AM
[SOLVED] Auto updating text boxes by selecting value from combo box ThomasK Forms 2 05-06-2003 05:39 PM




All times are GMT -8. The time now is 12:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World