SubForm Refreshing After Table Updates

[Crdmster]

Attached is a rough example of the problem I can't seem to overcome throughout my database. All help and/or advice is greatly appreciated. In essence, I'm not able to determine a repeatable way to refresh the Form/SubForm after adding a value to atable via VBA. I've tried different Form refresh methods, Me.Refresh methods, and also tried closing all forms and reopening (all within the VBA code). Using Me.Refresh works some of the times, but I believe lag in the system occasionally makes it so the refresh does not work. I've found that I can add a "Pause" in the code before refreshing and that resolves the problem, but that's not an efficient solution since I would have to tell the system to wait up to 10 seconds each time a button edits a table.



The "Refresh All" button on Access top ribbon works perfectly, but I can't expect users to click that button each time a change has been implement.

Issue:
After adding a new comment to tblComments via the "Add New Comment" button, the data is added to the appropriate table and query, but I can't find a reliable way to refresh/requery/repaint the subForm.

Question:
What are the recommended methods to adding values to tables and instantaneously updating the query in the subform.


Condensed Code Used to Insert New Comments to Table:

 
strTable= "tblComments"
strFields="[ProjectName], [Date], [UserName], [Comment]"
strValues=""XXX", "6/1/2022" , "XXX", "Test Comment"
strSQL = "INSERT INTO " & strTable & " (" & strFields & ") VALUES (" & strValues & ");"
DoCmd.RunSQL strSQL

Database Example.pdf

[Micron]

Refresh in vba updates current recordset - it does not load new records, nor does it remove deleted ones from view. Use Requery for that.

[Gicu]

I agree that Requery of the subform would work:

Me.frmSubComments.Form.Requery

But that will reposition the record to the first record of the subform (which might not be a problem for you as you seem to have the subform as a datasheet or continuous form). But if you need to stay on the same record you can try to requery the recordset of the subform:

Me.frmSubComments.Form.Recordset.Requery

Cheers,