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
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
I am having trouble requeying a calculated field in a main form after
updating data in a subform. There are many posts relating to requery however I am a novice to db programming and the solutions assume too more knowledge. The structure of the DB is: The main form (pricing summary) is based on a query that includes fields from multiple tables as well as calculated fields. One of the data items is a 'quote' field which forms the basis of the fields relating to pricing: profit, margin, tax. I have two main questions: 1) I could not make the quote field updatable in the main form so included a subform with this data which was updatable. Is there a simple way of making particular fields in the main form updatable when the form is based on a query? 2) If not, how can I requery the pricing statistics (calculated fields in the base query) when the quote is changed. Basically the user want to view the data, look at the current quote, see how much profit will be made and play around with the quote value until they are happy with the profit margin. The only solution currently is to include a save button in the sub form to force a save and for the user to try 'requery all' in teh access 2007 toolbar. Any direction would be greatly appreciated. Ive seen suggestions for similar issues along the lines of: Me.Dirty = False 'save record with new value Me.[calculated text box].Requery But not sure where to include this. Directly in the event code for the field that the user is updating? |
#2
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
Recalc will update the calculated fields.
If the code is in the subform's module, try: Me.Parent.Recalc If its in the main form's module, just: Me.Recalc -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "wintera" u57618@uwe wrote in message news:a262f196102fa@uwe... I am having trouble requeying a calculated field in a main form after updating data in a subform. There are many posts relating to requery however I am a novice to db programming and the solutions assume too more knowledge. The structure of the DB is: The main form (pricing summary) is based on a query that includes fields from multiple tables as well as calculated fields. One of the data items is a 'quote' field which forms the basis of the fields relating to pricing: profit, margin, tax. I have two main questions: 1) I could not make the quote field updatable in the main form so included a subform with this data which was updatable. Is there a simple way of making particular fields in the main form updatable when the form is based on a query? 2) If not, how can I requery the pricing statistics (calculated fields in the base query) when the quote is changed. Basically the user want to view the data, look at the current quote, see how much profit will be made and play around with the quote value until they are happy with the profit margin. The only solution currently is to include a save button in the sub form to force a save and for the user to try 'requery all' in teh access 2007 toolbar. Any direction would be greatly appreciated. Ive seen suggestions for similar issues along the lines of: Me.Dirty = False 'save record with new value Me.[calculated text box].Requery But not sure where to include this. Directly in the event code for the field that the user is updating? |
#3
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
On another point, Many queries may be updated, but some may not. For reasons
why not: http://allenbrowne.com/ser-61.html The problem is not that the form is based on a query in general, but that it is based on a particular query. If you cannot update a form based on a query, try opening the query directly. If you cannot edit the query, there is the problem. The only real solution is to devise a query that can be updated. The details depend on your database's structure. wintera wrote: I am having trouble requeying a calculated field in a main form after updating data in a subform. There are many posts relating to requery however I am a novice to db programming and the solutions assume too more knowledge. The structure of the DB is: The main form (pricing summary) is based on a query that includes fields from multiple tables as well as calculated fields. One of the data items is a 'quote' field which forms the basis of the fields relating to pricing: profit, margin, tax. I have two main questions: 1) I could not make the quote field updatable in the main form so included a subform with this data which was updatable. Is there a simple way of making particular fields in the main form updatable when the form is based on a query? 2) If not, how can I requery the pricing statistics (calculated fields in the base query) when the quote is changed. Basically the user want to view the data, look at the current quote, see how much profit will be made and play around with the quote value until they are happy with the profit margin. The only solution currently is to include a save button in the sub form to force a save and for the user to try 'requery all' in teh access 2007 toolbar. Any direction would be greatly appreciated. Ive seen suggestions for similar issues along the lines of: Me.Dirty = False 'save record with new value Me.[calculated text box].Requery But not sure where to include this. Directly in the event code for the field that the user is updating? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#4
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
Thanks for the pointer. I can see from this link that the query is not
updatable due to grouping and the nature of the linked tables so the solution I have will have to stand. BruceM wrote: On another point, Many queries may be updated, but some may not. For reasons why not: http://allenbrowne.com/ser-61.html The problem is not that the form is based on a query in general, but that it is based on a particular query. If you cannot update a form based on a query, try opening the query directly. If you cannot edit the query, there is the problem. The only real solution is to devise a query that can be updated. The details depend on your database's structure. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#5
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
Thanks for this. I expect to put the code in the 'after update' property of
the Quote field of subform so me.parent.recalc looks like the way to go. Is Parent the name of the parent form or a standard term? Is Me and Recalc standard text so not varied from DB to DB? Allen Browne wrote: Recalc will update the calculated fields. If the code is in the subform's module, try: Me.Parent.Recalc If its in the main form's module, just: Me.Recalc I am having trouble requeying a calculated field in a main form after updating data in a subform. There are many posts relating to requery [quoted text clipped - 42 lines] field that the user is updating? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#6
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
On Thu, 21 Jan 2010 02:51:13 GMT, "wintera via AccessMonster.com" u57618@uwe
wrote: Is Parent the name of the parent form or a standard term? Is Me and Recalc standard text so not varied from DB to DB? Parent, Me, and Recalc are all builtin terms: Me means "the current form", Parent means (in this context) "the parent form containing this form in a subform control", and Recalc is a standard action. You can highlight any of these (or any other term) in the VBA editor and press F1 to jump to the Help topic on the term. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
The AfterUpdate event of the text box will be too early: the record hasn't
been saved yet. Try the form's AfterUpdate event of the form (that is, Form_AfterUpdate in the subform.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "wintera via AccessMonster.com" u57618@uwe wrote in message news:a26becf8d7068@uwe... Thanks for this. I expect to put the code in the 'after update' property of the Quote field of subform so me.parent.recalc looks like the way to go. Is Parent the name of the parent form or a standard term? Is Me and Recalc standard text so not varied from DB to DB? Allen Browne wrote: Recalc will update the calculated fields. If the code is in the subform's module, try: Me.Parent.Recalc If its in the main form's module, just: Me.Recalc I am having trouble requeying a calculated field in a main form after updating data in a subform. There are many posts relating to requery [quoted text clipped - 42 lines] field that the user is updating? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
#8
|
|||
|
|||
requerying a main form calculated fields from after subform data entry
Or maybe use a subform for what is now grouped data. That means you would
need a separate linked table for that data. You have not offered many details of the structure, so it is not possible to be specific. wintera wrote: Thanks for the pointer. I can see from this link that the query is not updatable due to grouping and the nature of the linked tables so the solution I have will have to stand. On another point, Many queries may be updated, but some may not. For reasons why not: [quoted text clipped - 5 lines] problem. The only real solution is to devise a query that can be updated. The details depend on your database's structure. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201001/1 |
Thread Tools | |
Display Modes | |
|
|