Form.Refresh not working (.requery does)

[Bolduque]

Using MS Access 2016 together with MSSQL 2012 backend via linked tables.



I have an issue which suddenly popped up which I seem to be unable to resolve.

I have a set of linked tables (which can be either tables or views) within the access db.

For at least one of these linked tables I have a form which has a second linked table as a subdatasheet. All of this works fine.

What I now want to do is after updating a certain field on the linked subform, I want the parent form to be refreshed but not requeried (as the changes in the subform will not cause new or deleted rows in the parent). This however does not work.

In my subform's control.afterupdate() event I have placed a:

me.parent.form.refresh

I see that this refreshes all the records in the subform correctly (which makes sense as refreshesing the parent form should also refresh the linked subform), however nothing changes in the parent form.

If I were to say me.parent.form.requery then I lose my spot in the parent form (due to the requery) but the changes are correctly reflected.

If i put a break on the me.parent.form.refresh and I take a look at the view in the sql database I see that the fields have changed (indicating that this is not a timing issue), it just is not visible in access.

A few weeks ago when I first designed these forms I am 100% sure that this was working, but somehow this does not function anymore. Does anyone have any ideas?

If required I can provide more information, thanks in advance for taking a look!

[isladogs]

Depending on what has been changed, using recalc may work

[Bolduque]

I'm afraid that a .recalc (or even a refresh followed by a recalc) also does not work. There are no MS Access calculated fields, everything is part of linked tables from MSSQL

[isladogs]

Thought you'd say that.

In that case. bookmark the current record then requery and finally go to the bookmarked record
If you don't know how to do that, run a forum or Google search - you'll find loads of examples
You may wish to turn off screen updating first to stop flickering then turn on again afterwards (Application.Echo False ... then True)

[Bolduque]

Yeah that is indeed a solution area, would you (or anyone else) know why this doesn't work though? The refresh is quicker and more responsive than the recalc. I have a feeling that I will be ending up with the requery though I truly am interested in why this does not work, especially as I have seen it work a few weeks ago before my holidays

[isladogs]

The situation you are describing is something I have experienced as well.
You could try each of the following workrounds and see if any of them help
1. Set the focus on the parent form then refresh it.
2. Move to the previous record in the parent then return to the record you had originally.
3. Force a save in the parent table after setting the focus : If Me.Dirty Then Me.Dirty =False
4. Try changing the form recordset type to Dynaset (inconsistent updates)

Suggest you backup the form and subform first

[Micron]

there is an accepted solution here
https://www.accessforums.net/showthread.php?t=59150

sorry, just noticed that involved a requery, but the simple idea may work for refresh as well