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
|
|||
|
|||
Storing calculated fields
I simply can't seem to be able to enter two values into a form (and thus,
into a table) and then have their product shown in another field and ALSO stored in that table. Having the calculation result appear in the form is not a problem. The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. It may seem redundant to store this result, but it's necessary in this case. Any suggestions you may have would be appreciated. THis is very important to the cause. -- Norm Shimmel Butler, PA |
#2
|
|||
|
|||
Storing calculated fields
The solution to your problem is to use code in the AfterUpdate event of your
two values to assign the results to the control that is bound to your 3rd field. Me.ThirdControl = Me.FirstControl * Me.SecondControl You may want to test for valid values in the two controls first. NES wrote: I simply can't seem to be able to enter two values into a form (and thus, into a table) and then have their product shown in another field and ALSO stored in that table. Having the calculation result appear in the form is not a problem. The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. It may seem redundant to store this result, but it's necessary in this case. Any suggestions you may have would be appreciated. THis is very important to the cause. -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200801/1 |
#3
|
|||
|
|||
Storing calculated fields
"NES" wrote:
... The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. That's not a problem, it's Access' way of nudging you toward good DB design. You can always simulate the presence of the calculated field by using a query instead of a table for whatever downstream purpose. So instead of a table with fields A, B and AtimesB, you have only the fields A and B, and use a query like so: SELECT A, B, A*B AS AtimesB FROM... In the event you need the product for some purpose (say as an index or PK), then store only A and product in the table, and use a query like: SELECT A, AtimesB/A as B, AtimesB But if absolutely positively insist on redundantly storing all three values, you can bind the Product control of the form to a table field and use VBA to set its value, using the AfterUpdate events of text boxes holding values A and B. -- TedMi |
#4
|
|||
|
|||
Storing calculated fields
Thank you very much for confirming what I suspected. s Actually, I
mis-stated the situation. The table is storing medications and the unit price of the same, but not the TotalCost. Initially, as these are entered as new items, the Quantity and UnitPrice are stored in the table, which is the result of TotalCost divided by Quantity. This is done via a special form, used only for new medications. Thereafter, as inventory is relieved by dispensing, the transaction is stored in a separate update table in QuantityDispensed and UnitCost fields. Additional info is stored in a separate table for patient/medication tracking. When new medications arrive and are entered, they are entered into a separate update table of similar structure where the "quantity to add" and a now "recalculated" UniteCost is to be temporarily stored. Once a day, or more frequently if desired, a macro will run two update queries, one will will relieve inventory of all medications dispensed, and the other will post the increase in inventory and the new unit cost. Then the two update tables are cleared of data and ready to receive additional updates. I use a query for the inventory addition because the person entereing it must verify the NDC# (National Drug Code), the source of which is the Master Drug table, and the updated info is to be stored in the update table until the update query is run. It seems to be the most efficient way of doing this. But I may have to reconsider. If all of this seems confusing, I apologize. Drug tracking and patient information involves different problems that must be handled together, and I'm doing the best that I can. Thanks again for your information. I can probably do something now that you confirm the situation. -- Norm Shimmel Butler, PA "tedmi" wrote: "NES" wrote: ... The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. That's not a problem, it's Access' way of nudging you toward good DB design. You can always simulate the presence of the calculated field by using a query instead of a table for whatever downstream purpose. So instead of a table with fields A, B and AtimesB, you have only the fields A and B, and use a query like so: SELECT A, B, A*B AS AtimesB FROM... In the event you need the product for some purpose (say as an index or PK), then store only A and product in the table, and use a query like: SELECT A, AtimesB/A as B, AtimesB But if absolutely positively insist on redundantly storing all three values, you can bind the Product control of the form to a table field and use VBA to set its value, using the AfterUpdate events of text boxes holding values A and B. -- TedMi |
#5
|
|||
|
|||
Storing calculated fields
Thank you very much for your reply. I'm trying to avoid the use of code where
I can. I'm not that familiar with VBA. I have use the AfterUpdate property in a form property in the past to change the update date field. I'll take a look at that. Thanks again -- Norm Shimmel Butler, PA "ruralguy via AccessMonster.com" wrote: The solution to your problem is to use code in the AfterUpdate event of your two values to assign the results to the control that is bound to your 3rd field. Me.ThirdControl = Me.FirstControl * Me.SecondControl You may want to test for valid values in the two controls first. NES wrote: I simply can't seem to be able to enter two values into a form (and thus, into a table) and then have their product shown in another field and ALSO stored in that table. Having the calculation result appear in the form is not a problem. The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. It may seem redundant to store this result, but it's necessary in this case. Any suggestions you may have would be appreciated. THis is very important to the cause. -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200801/1 |
#6
|
|||
|
|||
Storing calculated fields
Hi Norm,
There are pleanty of posters here that can assist with the code you are looking for. Feel free to post back if you need further assistance on this issue. NES wrote: Thank you very much for your reply. I'm trying to avoid the use of code where I can. I'm not that familiar with VBA. I have use the AfterUpdate property in a form property in the past to change the update date field. I'll take a look at that. Thanks again The solution to your problem is to use code in the AfterUpdate event of your two values to assign the results to the control that is bound to your 3rd [quoted text clipped - 13 lines] Any suggestions you may have would be appreciated. THis is very important to the cause. -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Storing calculated fields
ok.
Użytkownik "NES" napisał w wiadomości ... Thank you very much for confirming what I suspected. s Actually, I mis-stated the situation. The table is storing medications and the unit price of the same, but not the TotalCost. Initially, as these are entered as new items, the Quantity and UnitPrice are stored in the table, which is the result of TotalCost divided by Quantity. This is done via a special form, used only for new medications. Thereafter, as inventory is relieved by dispensing, the transaction is stored in a separate update table in QuantityDispensed and UnitCost fields. Additional info is stored in a separate table for patient/medication tracking. When new medications arrive and are entered, they are entered into a separate update table of similar structure where the "quantity to add" and a now "recalculated" UniteCost is to be temporarily stored. Once a day, or more frequently if desired, a macro will run two update queries, one will will relieve inventory of all medications dispensed, and the other will post the increase in inventory and the new unit cost. Then the two update tables are cleared of data and ready to receive additional updates. I use a query for the inventory addition because the person entereing it must verify the NDC# (National Drug Code), the source of which is the Master Drug table, and the updated info is to be stored in the update table until the update query is run. It seems to be the most efficient way of doing this. But I may have to reconsider. If all of this seems confusing, I apologize. Drug tracking and patient information involves different problems that must be handled together, and I'm doing the best that I can. Thanks again for your information. I can probably do something now that you confirm the situation. -- Norm Shimmel Butler, PA "tedmi" wrote: "NES" wrote: ... The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. That's not a problem, it's Access' way of nudging you toward good DB design. You can always simulate the presence of the calculated field by using a query instead of a table for whatever downstream purpose. So instead of a table with fields A, B and AtimesB, you have only the fields A and B, and use a query like so: SELECT A, B, A*B AS AtimesB FROM... In the event you need the product for some purpose (say as an index or PK), then store only A and product in the table, and use a query like: SELECT A, AtimesB/A as B, AtimesB But if absolutely positively insist on redundantly storing all three values, you can bind the Product control of the form to a table field and use VBA to set its value, using the AfterUpdate events of text boxes holding values A and B. -- TedMi |
#8
|
|||
|
|||
Storing calculated fields
If that doesn't work you might try using a recordset.
"NES" wrote in message ... Thank you very much for your reply. I'm trying to avoid the use of code where I can. I'm not that familiar with VBA. I have use the AfterUpdate property in a form property in the past to change the update date field. I'll take a look at that. Thanks again -- Norm Shimmel Butler, PA "ruralguy via AccessMonster.com" wrote: The solution to your problem is to use code in the AfterUpdate event of your two values to assign the results to the control that is bound to your 3rd field. Me.ThirdControl = Me.FirstControl * Me.SecondControl You may want to test for valid values in the two controls first. NES wrote: I simply can't seem to be able to enter two values into a form (and thus, into a table) and then have their product shown in another field and ALSO stored in that table. Having the calculation result appear in the form is not a problem. The problem is the field control cannot be assigned to the table AND be the result of a calculation it seems. It may seem redundant to store this result, but it's necessary in this case. Any suggestions you may have would be appreciated. THis is very important to the cause. -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200801/1 |
Thread Tools | |
Display Modes | |
|
|