How do you get a Microsoft Access form to refresh a computed column?

Question

Hello,

I have a Microsoft Access form that is bound to a linked SQL Server table that has a computed column. I have a control bound to the computed column.

I already realize that the computed field cannot be refreshed until AFTER a record is saved. Beyond that, what is the best way to refresh that textbox that is bound to the computed column after save.

I would prefer not to do a me.requery (a requery of the whole recordset).

Is there a way to JUST refresh that one field?

Thanks. Seth

Answer 1

EDITED FOR CLARITY: There are actually a few strategies to consider.

Form.Refresh() will refresh your Form's recordsource capturing modifications and deletions to existing records and will stay positioned on the current record. However, you would not see any NEW records that were added since you opened your form.

Form.Requery() will re-run the Form's recordsource query. You will see all the Form.Refresh() changes AND it will show you any new records. On the UI, Form.Requery() repositions to the first record.

Form.Control.Requery() is similar to Form.Refresh() in that you will not change record position or see NEW records. It will update your control, assuming the control is based on a query/table.

You'll need to be sure that the triggering event involves a database update. There can be cases where the control's AfterUpdate() precedes database I/O, which wouldn't help you.

Answer 2

Have you tried .Refresh for either the underlying recordset, or for the bound control? I would expect it to work for a Jet/ACE back end, but the interaction with different database server back ends is going to be non-predictable.

There might also be an interaction with you ODBC refresh interval, but I would expect a manual refresh in code to take care of that.

Where to call it is another issue -- I would assume the appropriate place would be in the AfterUpdate events of the controls bound to the fields the calculation is based on. But you might have to save the record for the server-side calculation to happen -- Refresh alone might or might not do the trick. I know with Jet/ACE data sources a Refresh saves the record, but I don't know for certain if it behaves the same way with ODBC data sources.

Answer 3

Thanks guys. Here it what worked.

In the Form AfterUpdate event I did a Me.ControlName.Requery. This was perfect as it did not do a complete form refresh or requery.

I just experimented with different event/method combinations until I got the best result.

Thanks for the input.

Seth