Subform autorefreshes when buund to Access table, but not when bound to SQL Server table

[keviny04]

I have a form set up as shown in the picture below. The subform on the left is bound to a local Access table. The subform on the right is bound to a local SQL Server 2014 Express table. Btw, this database serves no real-world purposes. It is specially made just for me to ask the following empirical question.



If I update the textbox Field1 and click Save, the button's OnClick VBA code will update (via OpenRecordset, Edit, Update, etc.) the value of Field1 in both tables to which the two subforms are bound.

It will also set focus on both forms:

Me!subform1.SetFocus
Me!subform2.SetFocus

For reasons I don't really understand, when subform1 (which is bound to Access table) gets the focus, it auto-refreshes with the change I made to Field1 earlier.

And here is my question: when subform2 (bound to SQL Server table) gets the focus, it doesn't auto-refresh likewise; why is that?

And if I try to go into subform2 to edit Field1 directly, I get the error saying the value has been updated by another user, etc.

Does SQL Server has something to do with all this? Is there any way to make subform2 auto-refresh likewise?

Obviously, I can't upload my database since it is linked to SQL Server.

[pbaldy]

I'm actually surprised the subform bound to the local table refreshes itself, but I work mainly with SQL Server back ends. You can force the issue with:

Me!subform2.Form.Requery

The write conflict can happen when you have a form bound to a table that you also update via code. The requery may resolve that.

[keviny04]

I'm actually surprised the subform bound to the local table refreshes itself, but I work mainly with SQL Server back ends. You can force the issue with:

Me!subform2.Form.Requery

The write conflict can happen when you have a form bound to a table that you also update via code. The requery may resolve that.

Hi pbaldy, is there a way without using Requery? I'm in a situation where Requery is undesirable. In my real-world application, I have a subform with thousands of records. Requery would move the record pointer to the top, and I would have to use code to move it back to the record shown before. All this record pointer movement up and down would create an ungainly sight for the user. Is there a way to make it autorefresh just like the other subform does?

[pbaldy]

Nothing comes immediately to mind. That's a lot of records to pull across the network. I'd be looking at some way of limiting what's in the subform at any given time. Can it be limited by day/customer/employee/whatever so that it's not pulling that many records at once? It would seem a likely candidate for performance problems.