requerying a main form calculated fields from after subform data entry

[wintera]

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?

 

[Allen Browne]

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

 

[BruceM via AccessMonster.com]

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.

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?

 

[wintera via AccessMonster.com]

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.

 

[wintera via AccessMonster.com]

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?

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?

 

[John W. Vinson]

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.

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

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?

 

[BruceM via AccessMonster.com]

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.

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.