If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how get calculated amount from form to underlaying table
I have employee expense database. Due large amounts foreign travel, want to
add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
#2
|
|||
|
|||
how get calculated amount from form to underlaying table
You have a couple of options here,
1) store the exchange rate information, including the date it was in effect. You could then use this to redo the calculation when necessary. 2) store the calculated value. To store the calculated value, create a hidden textbox bound to the field you want to store the value in. In the form's BeforeUpdate event, copy the value from the calculated textbox to the hidden textbox. This value will now be stored in the table when the record is saved. There is another potential problem, what will you do when you review the records? Do you use a different form that will have a textbox bound to the field you stored the calculated value in? -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... I have employee expense database. Due large amounts foreign travel, want to add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
#3
|
|||
|
|||
how get calculated amount from form to underlaying table
One of the basic rules of database design is never store a calculated value.
You have all the data necessary to calculate the amount in the base currency and the ability to display it. That is all you need. Any time you need to display it, whether on this form, another form, a report, on in a query, calculate it at that point. You are already doing the calculation for a new record, so you have that under control. To display it for records already in the database, use the form's Current event to do the same calculation. In fact, I would suggest you put the calculation routine in a function in a standard module so you can call it from anywhere and the calculation will be consistant across your application. Now that the preaching is over and you are frustrated because I haven't told you how to do it, I will tell you how, but strongly suggest you don't do it. If you have a field in your table for storing the calculation, just bind the control where you display the calculated value to that field. And one last word.... Don't do it. "slowuptake" wrote: I have employee expense database. Due large amounts foreign travel, want to add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
#4
|
|||
|
|||
how get calculated amount from form to underlaying table
Thanks to both Wayne Morgan and Klatuu.
I believe you have both given essentially the same advice. I'd like ask one or both of you one followup question (which requires some explanation). From financial accounting standpoint: 1) the original amount and currency is important from standpoint of auditing 2) concurrency conversion of a single transaction is made only once, and once converted, the calculated value in your base currency is by definition the correct one. As a matter of standard practice for the company, employee expenses are organised by calendar month. Currency conversion is accomplished by looking up an average rate exchange rate over that month. I have created a database table that stores exchange rate data by currency and by month. So, provided nothing changes in the currency database, I can see that I could always re-create the calculation. But, if the currency database ever alters, then I will have a gap in the audit trail. However, if I store the calculated value and the original value, then there can never be a gap in the audit trail (and the conversion rate used is implicit in the two currency values). I am not an expert in databases or in accounting, however, in the comprehensive accounting database programmes I've used, they go to great lengths to ensure that a currency conversion for individual transactions can't be changed. So the question == It did (and still does) seem to me that it is correct to hard wire the currency conversion calculation into the data. However, I understand your advice and see that it can work provided nothing ever changes the currency conversion table. I light of my accounting/auditing concern, do you still advise me not to store the calculated value? regards, slowuptake "Klatuu" wrote: One of the basic rules of database design is never store a calculated value. You have all the data necessary to calculate the amount in the base currency and the ability to display it. That is all you need. Any time you need to display it, whether on this form, another form, a report, on in a query, calculate it at that point. You are already doing the calculation for a new record, so you have that under control. To display it for records already in the database, use the form's Current event to do the same calculation. In fact, I would suggest you put the calculation routine in a function in a standard module so you can call it from anywhere and the calculation will be consistant across your application. Now that the preaching is over and you are frustrated because I haven't told you how to do it, I will tell you how, but strongly suggest you don't do it. If you have a field in your table for storing the calculation, just bind the control where you display the calculated value to that field. And one last word.... Don't do it. "slowuptake" wrote: I have employee expense database. Due large amounts foreign travel, want to add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
#5
|
|||
|
|||
how get calculated amount from form to underlaying table
Historical tracking is one reason to store a calculated value. However,
rather than worrying about the conversion factor table changing, I would let it change. Instead I would also modify how it changes. I would include a date field that would indicate when the change was made, that way you would know the value on the date the transaction was made, even if the rate for that month was changed later; especially since you're keeping that data already anyway. (i.e. each change should be a new record in conversion table) -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... Thanks to both Wayne Morgan and Klatuu. I believe you have both given essentially the same advice. I'd like ask one or both of you one followup question (which requires some explanation). From financial accounting standpoint: 1) the original amount and currency is important from standpoint of auditing 2) concurrency conversion of a single transaction is made only once, and once converted, the calculated value in your base currency is by definition the correct one. As a matter of standard practice for the company, employee expenses are organised by calendar month. Currency conversion is accomplished by looking up an average rate exchange rate over that month. I have created a database table that stores exchange rate data by currency and by month. So, provided nothing changes in the currency database, I can see that I could always re-create the calculation. But, if the currency database ever alters, then I will have a gap in the audit trail. However, if I store the calculated value and the original value, then there can never be a gap in the audit trail (and the conversion rate used is implicit in the two currency values). I am not an expert in databases or in accounting, however, in the comprehensive accounting database programmes I've used, they go to great lengths to ensure that a currency conversion for individual transactions can't be changed. So the question == It did (and still does) seem to me that it is correct to hard wire the currency conversion calculation into the data. However, I understand your advice and see that it can work provided nothing ever changes the currency conversion table. I light of my accounting/auditing concern, do you still advise me not to store the calculated value? |
#6
|
|||
|
|||
how get calculated amount from form to underlaying table
After studying practical implications of advice from both Wayne and Klatuu, I
think I'll store the calculated value - but manually instead of automatically. I've started to turn my attention to a help facility that sits within the employee expense input form, contains both foreign exchange rates and agreed rates and values for the different projects, and allows employee to quickly calculate expenses ... but requires ultimate expense values and descriptions to be manually input by user. Thank you both for your advice. It changed my approach to the problem in a useful way. regards, slowuptake "Wayne Morgan" wrote: Historical tracking is one reason to store a calculated value. However, rather than worrying about the conversion factor table changing, I would let it change. Instead I would also modify how it changes. I would include a date field that would indicate when the change was made, that way you would know the value on the date the transaction was made, even if the rate for that month was changed later; especially since you're keeping that data already anyway. (i.e. each change should be a new record in conversion table) -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... Thanks to both Wayne Morgan and Klatuu. I believe you have both given essentially the same advice. I'd like ask one or both of you one followup question (which requires some explanation). From financial accounting standpoint: 1) the original amount and currency is important from standpoint of auditing 2) concurrency conversion of a single transaction is made only once, and once converted, the calculated value in your base currency is by definition the correct one. As a matter of standard practice for the company, employee expenses are organised by calendar month. Currency conversion is accomplished by looking up an average rate exchange rate over that month. I have created a database table that stores exchange rate data by currency and by month. So, provided nothing changes in the currency database, I can see that I could always re-create the calculation. But, if the currency database ever alters, then I will have a gap in the audit trail. However, if I store the calculated value and the original value, then there can never be a gap in the audit trail (and the conversion rate used is implicit in the two currency values). I am not an expert in databases or in accounting, however, in the comprehensive accounting database programmes I've used, they go to great lengths to ensure that a currency conversion for individual transactions can't be changed. So the question == It did (and still does) seem to me that it is correct to hard wire the currency conversion calculation into the data. However, I understand your advice and see that it can work provided nothing ever changes the currency conversion table. I light of my accounting/auditing concern, do you still advise me not to store the calculated value? |
#7
|
|||
|
|||
how get calculated amount from form to underlaying table
Although I would take Wayne's approach, I can understand your decision to
store the value. However, from an audit standpoint, will you be able to demonstrate to an auditor how the calculation was done and what the exchange rate was at the time of the calculation. That would be even more important to the auditor than would be the actual value. Now, the way to store it programmatically would be to bind a textbox on your form to the field in the table where your calculated amount will be carried. Since it is a calculated amount, you would at least want the text box on the form to be locked so it could not be changed manually. If you don't want the users to see that value, then make the text box invisible. In both cases, you will want the tab stop property of the text box to be No. "slowuptake" wrote: After studying practical implications of advice from both Wayne and Klatuu, I think I'll store the calculated value - but manually instead of automatically. I've started to turn my attention to a help facility that sits within the employee expense input form, contains both foreign exchange rates and agreed rates and values for the different projects, and allows employee to quickly calculate expenses ... but requires ultimate expense values and descriptions to be manually input by user. Thank you both for your advice. It changed my approach to the problem in a useful way. regards, slowuptake "Wayne Morgan" wrote: Historical tracking is one reason to store a calculated value. However, rather than worrying about the conversion factor table changing, I would let it change. Instead I would also modify how it changes. I would include a date field that would indicate when the change was made, that way you would know the value on the date the transaction was made, even if the rate for that month was changed later; especially since you're keeping that data already anyway. (i.e. each change should be a new record in conversion table) -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... Thanks to both Wayne Morgan and Klatuu. I believe you have both given essentially the same advice. I'd like ask one or both of you one followup question (which requires some explanation). From financial accounting standpoint: 1) the original amount and currency is important from standpoint of auditing 2) concurrency conversion of a single transaction is made only once, and once converted, the calculated value in your base currency is by definition the correct one. As a matter of standard practice for the company, employee expenses are organised by calendar month. Currency conversion is accomplished by looking up an average rate exchange rate over that month. I have created a database table that stores exchange rate data by currency and by month. So, provided nothing changes in the currency database, I can see that I could always re-create the calculation. But, if the currency database ever alters, then I will have a gap in the audit trail. However, if I store the calculated value and the original value, then there can never be a gap in the audit trail (and the conversion rate used is implicit in the two currency values). I am not an expert in databases or in accounting, however, in the comprehensive accounting database programmes I've used, they go to great lengths to ensure that a currency conversion for individual transactions can't be changed. So the question == It did (and still does) seem to me that it is correct to hard wire the currency conversion calculation into the data. However, I understand your advice and see that it can work provided nothing ever changes the currency conversion table. I light of my accounting/auditing concern, do you still advise me not to store the calculated value? |
#8
|
|||
|
|||
how get calculated amount from form to underlaying table
Item 2 - How do I copy the value from the calculated textbox to the hidden
textbox? I'm missing something basic here. I have a very similar situation in that my calculation is only on the form and I need to get it to a query for a report. "Wayne Morgan" wrote: You have a couple of options here, 1) store the exchange rate information, including the date it was in effect. You could then use this to redo the calculation when necessary. 2) store the calculated value. To store the calculated value, create a hidden textbox bound to the field you want to store the value in. In the form's BeforeUpdate event, copy the value from the calculated textbox to the hidden textbox. This value will now be stored in the table when the record is saved. There is another potential problem, what will you do when you review the records? Do you use a different form that will have a textbox bound to the field you stored the calculated value in? -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... I have employee expense database. Due large amounts foreign travel, want to add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
#9
|
|||
|
|||
how get calculated amount from form to underlaying table
Why not do the calculation in the query?
Storing calculated values in a table is almost always a bad idea. "JDJones" wrote: Item 2 - How do I copy the value from the calculated textbox to the hidden textbox? I'm missing something basic here. I have a very similar situation in that my calculation is only on the form and I need to get it to a query for a report. "Wayne Morgan" wrote: You have a couple of options here, 1) store the exchange rate information, including the date it was in effect. You could then use this to redo the calculation when necessary. 2) store the calculated value. To store the calculated value, create a hidden textbox bound to the field you want to store the value in. In the form's BeforeUpdate event, copy the value from the calculated textbox to the hidden textbox. This value will now be stored in the table when the record is saved. There is another potential problem, what will you do when you review the records? Do you use a different form that will have a textbox bound to the field you stored the calculated value in? -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... I have employee expense database. Due large amounts foreign travel, want to add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
#10
|
|||
|
|||
how get calculated amount from form to underlaying table
I guess I'm confusing myself. I need to pick a future date option in three
places on a table. The choices are to add 15, 30, 45, or 60 days to the date of an event (record). So I created a FutureDates Lookup Table. If I need the info in three places, I guess I'd need 3 FutureDates Lookup Tables (FutureDateTbl1, FutureDateTbl2, FutureDateTbl3)? I can't reference the same lookup table three times in the same query without them all being the same choice. I started down the path of putting an unbound combo box on the form for the lookup and the field calculated just fine. My problem was getting the result into the underlying query so I could use the result for reports. I guess the paragraph above makes the most sense. I was headed for SetValue in a hidden box, but guess this won't work either. I know you don't put calculations into tables. Forms and Reports lie, tables never do. "Klatuu" wrote: Why not do the calculation in the query? Storing calculated values in a table is almost always a bad idea. "JDJones" wrote: Item 2 - How do I copy the value from the calculated textbox to the hidden textbox? I'm missing something basic here. I have a very similar situation in that my calculation is only on the form and I need to get it to a query for a report. "Wayne Morgan" wrote: You have a couple of options here, 1) store the exchange rate information, including the date it was in effect. You could then use this to redo the calculation when necessary. 2) store the calculated value. To store the calculated value, create a hidden textbox bound to the field you want to store the value in. In the form's BeforeUpdate event, copy the value from the calculated textbox to the hidden textbox. This value will now be stored in the table when the record is saved. There is another potential problem, what will you do when you review the records? Do you use a different form that will have a textbox bound to the field you stored the calculated value in? -- Wayne Morgan MS Access MVP "slowuptake" wrote in message ... I have employee expense database. Due large amounts foreign travel, want to add foreign currency capacity into the expense input form - and record into the underlaying table both the original amount and currency and the amount in base currency. Foreign currency amount entered by way of text box - no problems. Combo box allows user to choose which foreign currency from a list - no problems. List box looks up current exchange rate based on combo box choice - no problems. A separate text box calculates and displays the amount in my base currency - no problems. Problem - I can't figure out how to get calculated amount to be automatically written into underlaying database. As temporary solution I now manually retype the result from last text box. I suspect the problem isn't that hard, but as usual, I'm ... slowuptake |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combo Box NotInList - How To Add Data To Underlying Table | 10SNUT | Using Forms | 19 | July 8th, 2005 09:12 PM |
Can I link 1 form to several tables? | Mico | Using Forms | 7 | July 6th, 2005 07:36 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |