After upsizing database to SQL, subform not refreshing after return from an update form

Question

Another post-upsize issue.  This one isn't critical, but I'd like to correct it if possible.

I have a subfile displaying various column values from Help Desk records, and the subfile is coded such that, if you double-click on a row, the form for maintaining that record is opened.  Upon making changes and closing the maintenance form, the subform used to reflect any changes to the record.  It no longer does so.  Any ideas on why having an SQL back-end, as opposed to Access, would cause this, or what might be done to fix it are welcome.

Answer 1

A few tips:

In your “edit” row button code, you want to ensure that “if” the current row can be modified by your form, then you need to ensure that you write the record to the table BEFORE you launch the form.

Eg:

If me.dirty = true then
   Me.dirty = false
End if

You may well not need the above code if no editing of the record can occur before you hit the edit button.

At this point, the your code likely to be:

Docmd.Openform "frmEditDetails",,,"id = " & me!id

Now, do you launch the above form as dialog or not? (very important). If you launch the form as dialog then you can use this code:

docmd.OpenForm "frmEditDetails",,,"id = " & me!id,,acDialog

me.refresh

So if  your code uses acDialog as per above, then the code will wait until that form is closed, and the next line of “me.Refresh” will thus re-fresh any updated records in that sub form.

So the final code would be:

If me.dirty = true then
   Me.dirty = false
End if

docmd.OpenForm "frmEditDetails",,,"id = " & me!id,,acDialog

me.refresh

However, if the current row button clicked on never allows editing in that sub form, then you don’t need the first bit above that checks for a dirty record. However if the sub form does allow editing and allows launching of that form, then both forms could “dirty” the record and you would receive a record changed conflict. So anytime you in a form that can launch anther form that “may” edit the same record, you always want to write out the changes to the table with me.dirty = false before you launch such additional forms.

>>? Any ideas on why having an SQL back-end, as opposed to Access, would cause this

Yes, it kind of asking why a bicycle behaves different than a nuclear powered submarine – they are rather different kinds of systems and technology.

When Access uses a “local” data engine like JET/ACE, then it is able to tell the form to “update”, but SQL server does not have that ability, since it might be a web site, vb.net, or Access that is the client consuming and updating that data on the server.

So you in “some” cases will have to modify your code to re-fresh a screen if some other form updates the data since SQL server does not really know or care if you updating data with Access, a web site, vb.net etc.

So SQL server does not “send” back some notification to the client that such and such table has been updated. However Access “does” attempt and “try” to determine if an update has occurred based on if a timestamp column is available (but the developer should attempt to advoid this need). As per above code example if the form is opened as dialog, then you only require 1 extra line of code (the me.refresh that follows the openform) to remedy these cases.

If you don’t want to open the form as dialog, and want to have the form as “modal” and not “dialog”, then you likely have to put code in the “close” event of that details form you launched from the sub form as modal to force a re-fresh.

Regards,
Albert D. Kallal (Access MVP, 2003 – June 2017)
Edmonton, Alberta Canada

Answer 2

Albert,  Thanks for the detailed explanation as to what is going on.