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?

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?

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

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

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

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]

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

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