Access Subform linked to selection in subform doesn't update

Question

I have a masterform that contains four subforms.

The first subform Activity Subform_DatasheetView is directly linked to the masterform Compliance and looks up records based on selections in three dropdowns.

The second subform CommentDatasheet_Subform shows the records associated with the record selected in the first subform.

The third subform Activity Subform_Detail shows details from the selected recrod in the first subform. The fourth subform works the same way for the third subform.

All of these subforms work, in so far as they show the details I want, but only after I manually select each subform and then refresh them.

Question: How can I get my subforms to automatically update/refresh? I have only seen the event option "On click" in the main form.

I ran into similar problems too. Now I create my own forms from a blank template and control all of the data flow myself using custom sql commands from vba. A lot more coding but it avoids the hassle and limitations of using subforms — Michael
I'm getting somewhat frustrated with these subforms myself. I initially created the first subform with the wizard, which named the subform based on the tables I was using, all of which have "tbl_" as a prefix. So that subform was called "tbl_xxxx subform"... a mess. I wanted to rename it... and now the connection is broken. Can't figure out what it is wrong. I adjusted the Master / Child Fields... still not working. I see why you would start using SQL as a solution. — rohrl77
I experienced the exact same problems. If you want to edit something it's almost as if you need to start from scratch with the subforms. Very frustrating. Definitely stick to SQL and handling all of the data input and validation yourself in VBA. It took me a couple hours to get used to using VBA as a replacement but I wouldn't look back. Sorry I couldn't help — Michael

Chawin Chawin · Accepted Answer · 2016-07-22T09:36:25

You can create an event on your subform to have the masterform refresh the other subforms when a record is changed, like so:

Subform_DatasheetView Form Code

Public Event RecordChanged()

Private Sub Form_Current()
    RaiseEvent RecordChanged()
End Sub

Compliance Form Code

Dim WithEvents m_Subform_DatasheetView As Form_Subform_DatasheetView

Private Sub Form_Load()
    m_SubForm_DatasheetView = Me.Subform_DatasheetView
End Sub

Private Sub m_Subform_DatasheetView_RecordChanged()
    CommentDatasheet_Subform.Requery()
    Subform_Detail.Requery()
End Sub

Access Subform linked to selection in subform doesn't update

1 Answers