Refresh main form from sub form

[Guest]

For reasons I won't go into I need to refresh the main form when I've entered
data on a sub form. However I also wish to stay on the current record of the
subform not go to the first record. I have tried using recordsetclone and
bookmarking before doing the refresh but the bookmark is null after refresh.

Can this be accomplished?

Thanks

Alan

 

[Allen Browne]

Refresh the main form? You changed the values in the main form and wanted to
force the changes to be saved? How about:
Me.Parent.Dirty = False

If you do anything that causes the main form to reload its record, that will
trigger a reload of the subform. The bookmark does not survive the reload,
so that's why you received that error. You could save the primary key value
of the subform record into a variable, and later FindFirst on the
RecordsetClone and set the Bookmark. But even if you do that, it is likely
to scroll the subform upwards so that the current record is the first one
displayed.

Hopefully you are not storing a value (like a total) in the main form that
depends on the values in the subform. There are very few cases where that
would be desirable.

 

[Guest]

Thanks Allen
In fact I was displaying a total on the main form based on values entered in
the sub form. The problem arose beacuse the sub form displayed the total
number of meals taken in a week by a diner. However the main form had to
show two totals a)meals paid for b)free meals this depended on the status of
the diner and so I had used a Dlookup to calculate each function. This was
fine when the form was first loaded but when the user changed the number of
meals on the subform the Dlookup wasn't refreshed. When I had to do the
Refresh on the main form I realised I was probably going the wrong way. Your
reply convinced me and I have now got the query behind the subform to
categorise the free and paid put bound controls on the datasheet and hidden
them, then totalled them and used the totals as datasource for total boxes on
the main control.

Thanks for making me see the error of my ways!

Alan

 

[Allen Browne]

Excellent. Good solution.

And when you need to force Access to update the calculated controls on a
form:
Me.Recalc