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 |
#11
|
|||
|
|||
how get calculated amount from form to underlaying table
If you could be more specific, we can resolve this. What I was suggesting
would work if structured correctly, not require a future dates table, and would run faster because it would not have to do the lookups. If I knew what you mean by needing the data in 3 places, it would be useful. Also, how do you know how many days to add? Essentially, you would use a calculated field in the query something like this: FutureDate: DateAdd("d", ??howmanyDays, originaldate) "JDJones" wrote: 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 |
#12
|
|||
|
|||
how get calculated amount from form to underlaying table
I know I'm late to this party but I have a related problem that I hope you
can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#13
|
|||
|
|||
how get calculated amount from form to underlaying table
I don't know if I fully understand everything you need to do; however, from
what I do understand, it appears you could put a command button on the main form and put the code in it's Click event to do whatever needs to be done. "natalie" wrote: I know I'm late to this party but I have a related problem that I hope you can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#14
|
|||
|
|||
how get calculated amount from form to underlaying table
Interesting..so if I follow you, creating a command button to display in a
text box from the calculated data from a text box on the subform isn't the same as storing the calculated field in the table. Yes? I think another reason I'm having a hard time getting the vb code right on this after update thing is that the calculated field is on the subform but the field I want it captured in is actually on the main form. "Klatuu" wrote: I don't know if I fully understand everything you need to do; however, from what I do understand, it appears you could put a command button on the main form and put the code in it's Click event to do whatever needs to be done. "natalie" wrote: I know I'm late to this party but I have a related problem that I hope you can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#15
|
|||
|
|||
how get calculated amount from form to underlaying table
"natalie" wrote: Interesting..so if I follow you, creating a command button to display in a text box from the calculated data from a text box on the subform isn't the same as storing the calculated field in the table. Yes? You are correct. Creating a table, populating it, and retrieving the data from it will be a lot slow and will add to database bloat. One technique to use would be to construct a query that would calculate the value you need, open it as a recordset, and copy the value into the text box. I think another reason I'm having a hard time getting the vb code right on this after update thing is that the calculated field is on the subform but the field I want it captured in is actually on the main form. It is just a matter of correct syntax to get what you want. Also, I would suggest you have a look at the orders form and orders sub form in the Northwind database. It has an example where there is a calculation performed on each row in the sub form that updates a text box (if I remember correctly) the subform footer section which is hidden, then picked up by a text box on the main form. See if something like that might work for you. "Klatuu" wrote: I don't know if I fully understand everything you need to do; however, from what I do understand, it appears you could put a command button on the main form and put the code in it's Click event to do whatever needs to be done. "natalie" wrote: I know I'm late to this party but I have a related problem that I hope you can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#16
|
|||
|
|||
how get calculated amount from form to underlaying table
Thanks for your help- I will see if I can get that to work.
"Klatuu" wrote: "natalie" wrote: Interesting..so if I follow you, creating a command button to display in a text box from the calculated data from a text box on the subform isn't the same as storing the calculated field in the table. Yes? You are correct. Creating a table, populating it, and retrieving the data from it will be a lot slow and will add to database bloat. One technique to use would be to construct a query that would calculate the value you need, open it as a recordset, and copy the value into the text box. I think another reason I'm having a hard time getting the vb code right on this after update thing is that the calculated field is on the subform but the field I want it captured in is actually on the main form. It is just a matter of correct syntax to get what you want. Also, I would suggest you have a look at the orders form and orders sub form in the Northwind database. It has an example where there is a calculation performed on each row in the sub form that updates a text box (if I remember correctly) the subform footer section which is hidden, then picked up by a text box on the main form. See if something like that might work for you. "Klatuu" wrote: I don't know if I fully understand everything you need to do; however, from what I do understand, it appears you could put a command button on the main form and put the code in it's Click event to do whatever needs to be done. "natalie" wrote: I know I'm late to this party but I have a related problem that I hope you can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#17
|
|||
|
|||
how get calculated amount from form to underlaying table
Wayne,
I have a quick question about your directions for storing the calculated field that I'm hoping you can clarify for me. My calculated field is on a subform footer that gives a tally for all those records on the subform. I need the stored calculated field on the MAIN form, so I have the sql statement =[frm subform test].Form!Text36 which, indeed, displays the same correct data as the subform footer field. However, how in the heck do I get it to actually save in the main form table? Specifically, you had written to write a code on the corm to copy the value fromtha calculated text box to the hidden text box but I don't know the vb jargon well enough to create that. Can you help? "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 |
#18
|
|||
|
|||
how get calculated amount from form to underlaying table
Hi, sorry, me again...so I should create a query, then create a macro that
runs the query and displays the data in a text box, and finally have the command button execute the whole thing. "Klatuu" wrote: "natalie" wrote: Interesting..so if I follow you, creating a command button to display in a text box from the calculated data from a text box on the subform isn't the same as storing the calculated field in the table. Yes? You are correct. Creating a table, populating it, and retrieving the data from it will be a lot slow and will add to database bloat. One technique to use would be to construct a query that would calculate the value you need, open it as a recordset, and copy the value into the text box. I think another reason I'm having a hard time getting the vb code right on this after update thing is that the calculated field is on the subform but the field I want it captured in is actually on the main form. It is just a matter of correct syntax to get what you want. Also, I would suggest you have a look at the orders form and orders sub form in the Northwind database. It has an example where there is a calculation performed on each row in the sub form that updates a text box (if I remember correctly) the subform footer section which is hidden, then picked up by a text box on the main form. See if something like that might work for you. "Klatuu" wrote: I don't know if I fully understand everything you need to do; however, from what I do understand, it appears you could put a command button on the main form and put the code in it's Click event to do whatever needs to be done. "natalie" wrote: I know I'm late to this party but I have a related problem that I hope you can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#19
|
|||
|
|||
how get calculated amount from form to underlaying table
Almost. You don't actually run the query. You use it as a recordset to
retrieve the values and populate the text box. "natalie" wrote: Hi, sorry, me again...so I should create a query, then create a macro that runs the query and displays the data in a text box, and finally have the command button execute the whole thing. "Klatuu" wrote: "natalie" wrote: Interesting..so if I follow you, creating a command button to display in a text box from the calculated data from a text box on the subform isn't the same as storing the calculated field in the table. Yes? You are correct. Creating a table, populating it, and retrieving the data from it will be a lot slow and will add to database bloat. One technique to use would be to construct a query that would calculate the value you need, open it as a recordset, and copy the value into the text box. I think another reason I'm having a hard time getting the vb code right on this after update thing is that the calculated field is on the subform but the field I want it captured in is actually on the main form. It is just a matter of correct syntax to get what you want. Also, I would suggest you have a look at the orders form and orders sub form in the Northwind database. It has an example where there is a calculation performed on each row in the sub form that updates a text box (if I remember correctly) the subform footer section which is hidden, then picked up by a text box on the main form. See if something like that might work for you. "Klatuu" wrote: I don't know if I fully understand everything you need to do; however, from what I do understand, it appears you could put a command button on the main form and put the code in it's Click event to do whatever needs to be done. "natalie" wrote: I know I'm late to this party but I have a related problem that I hope you can assist me with and I always benefit from reading your posts. I don't want to get too wordy and confusing but here's my situation: I have a form based on a query that includes a ysn field and specific txt field. There is a subform (continuous) that displays the sum from one subform record and then the total for all subform records in the subform footer. What I want to do is run a query based on the main form's ysn and txt fields that also displays the calculated subform footer. Once the query is run, I need the user to be able to click the ysn field in order to update it without having to go record by record in the form. The problem is, you can't click into the ysn field in the query. My only other option is to create the main form on a table, not a query, however, then I don't have access to the calculated field, which I desperately need. I can't do that, though, because I need to base the form on 2 tables. I tried creating a form without querying those 2 tables - but it didn't work because one of them has a primary key that I am using for a combo box that I need to have multiple records for. Have I lost you yet? So....if there is another way to store this data that I am unaware of that doesn't involve my user to have to run make table queries, alter ysn fields there and then run update queries (way too much room for error) then please, I'm "all ears!". Thanks in advance... "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 |
#20
|
|||
|
|||
how get calculated amount from form to underlaying table
Hi,
Could you please elaborate for me the vb code for the before update on the form? My calculated field is on the subform, which tallies the subform records. However, I need to store that value on the MAIN form. I have a total field that displays the correct data using a sql statement: =[frm subform test].Form!Text36 But the value doesn't store in that field. I create another txt box on the main table, added it to the form and bound the control to the total field, but it didn't store the value in the table. I'm obviously doing something very wrong. Can you help? "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 |