Refresh forms recordset source with custom SQL query keeping current selection after closing second form

Question

I have 2 forms in MS Access. One is an overview form as an endless form, the other shows details of the single records. The overview form gets its content by a custom SQL passthrough query on opening:

Dim strSQL As String
strSQL = "SELECT DISTINCT tbl_Parts.strPart, Title, Comment FROM [tbl_Parts] inner JOIN [tbl_PartModules] ON tbl_Parts.strPart = tbl_PartModules.strPart ORDER BY tbl_Parts.strPart DESC"
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
Dim strTmpConnQDF As String
strTmpConnQDF = CStr(Application.TempVars("tempvar_StrCnxn"))
qdf.Connect = strTmpConnQDF
qdf.sql = strSQL
qdf.ReturnsRecords = True

Set rst = qdf.OpenRecordset
Set Forms![frm_PartsOverview].Recordset = rst
Forms![frm_PartsOverview].Requery
'No rst.Close to have the data still in the Form!
Set qdf = Nothing

Within that overview, buttons can be used to change the underlying SQL query to show different records.

Each record has a "show details" button in the endless form which opens the detail form.

Within the detail form, I can change the data of the underlying record. After closing this form, I want to keep all settings (record selection, SQL query data) that were before opening the detail form. But I also want to update the underlying data, showing the changes made in the detail form also on the overview form.

I tried this code with the "hide" button of the detail form, but this does not work:

Forms!frm_PartsDetail.Visible = False
Forms!frm_PartsOverview.Recalc
Forms!frm_PartsOverview.Refresh
Forms!frm_PartsOverview.Requery

I tried all three variants (Recalc, Frequery, Refresh) but none of them did the trick. I guess its because of the way I assign the SQL query as the recordsource of the form. But how to update the recordsource, keeping all settings like they were before entering the detail form?

This should work. Try `Forms!frm_PartsOverview.Recordset.Requery`. Do not use Refresh method. It will reload all the records. Only use `Recordset.Requery`. — Harun24hr, Aug 14 '22 at 08:39
Does not work - error message "3251 operation not supported" — karlo922, Aug 14 '22 at 09:16
It seems that this is not possible with a passthrough-query according to this answer: https://stackoverflow.com/questions/67807594/me-recordset-requery-fails-with-error-3251-with-query-as-a-recordsource — karlo922, Aug 14 '22 at 09:22

Albert D. Kallal · Answer 1 · 2022-08-15T02:32:20.650

Well, you can use a me.Refresh - that will (should) show updates to the form. (but, the fact of it being basedon a pt query probably will effect this. I suggest changing to a linked view - thus no pt query required and thus access can stay on the given row, and you can use a me.refresh. (with a PT query and you having assigned the forms data source "one time", then me.refresh is unlikely to work.

So, I suggest using a linked view for that form. (and performance will be just as good at the pt query - and your me.Refresh will work (to show any changes made to the table).

the other way? Grab the current row (PK) before you do the me.Requery. Then do a find first on the pk row, and jump back to the row in question.

So, ignoring you have a sub form etc.

Then this code:

 Dim PK       As Long
 PK = Me!ID
 
 Me.Requery
 Dim rst      As DAO.Recordset
 Set rst = Me.Recordset
 
 rst.FindFirst ("ID = " & PK)

However, as noted, if you base that form on a linked view, and not set the datasource as you do, then a me.Refresh will show any changes made, and also stay on that current row.

So, in your case, to re-calc, re-display, and show any updates, and return to current row you were on?

"air code warning"

  Dim PK      As Long
  Dim f       As Form
  Dim rst     As DAO.Recordset
  
  Set f = Forms!frm_PartsDetail
  PK = f!ID
  f.Requery
  Set rst = f.Recordset
  rst.FindFirst ("ID = " & PK)

Refresh forms recordset source with custom SQL query keeping current selection after closing second form

1 Answers1