Search code examples
formsms-accessvbams-access-2010

Multiple Subforms in Datasheet View in Access

I have two subforms on my main Access Form, I am familiar with linking parent and child forms using the Link Master Fields, And Link Child Fields on each subform.

On my form, Subform1 is linked to an ID on the Main Form, and Subform2 is linked to an ID on Subform1.

I have previously got it working where if the user clicks on a record in subform 1, then Subform 2 gets dynamically updated to show specific data (this is when subform2 is set up as a Single Form View.

What I am trying to do, is get both subforms in datasheet view, and clicking on a record on subform1, should then requery subform2 for all related records of the item selected in subform2.

Is this possible with native Access functionality, Otherwise, is there a VBA solution?

thanks.

Solution

  • you won't be able to do it with native access functionality: Subform2 would need to be a child of subform1 for that.

    However you should be able to do it with VBA, readily enough.

    In the form displayed in the Subform1 control, create a Form_Current event. This will fire whenever the current record changes in Subform1. Then use that event to reset the recordsource of subform2, directly or indirectly.

    For example, suppose the MainForm shows information from a table called Person, and Subform1 shows information about contact episodes between persons and our organisation, and Subform2 details information about communication within these contact episodes.

    The data will be something like this:

    Mainform: SELECT * FROM Person
Subform1:   SELECT * FROM PersonEpisode
Subform2:     SELECT * FROM EpisodeCommunication

    The data in subform1 can be kept to that pertaining to just the person displayed on Mainform using LinkChild/Master as you know, or alternatively, Mainform's Form_Current event could be used to change the recordsource of subform1 like so:

    private sub Form_Current()    'on mainform
    Subform1.Form.RecordSource = "SELECT * FROM PersonEpisode WHERE PersonID = " & me.ID
end sub

    Likewise then, the form_Current in subform1 is then used to keep subform2 current to subform1:

    private sub Form_Current()    'in Subform1
    Forms!Mainform!Subform2.Form.Recordsource = "SELECT * FROM EpisodeCommunication WHERE EpisodeID = " & me.EpisodeID
end sub

    This should work fine. If you prefer you could instead:

    private sub Form_Current()    'in Subform1
    Me.Parent!Subform2.Form.Recordsource = "SELECT * FROM EpisodeCommunication WHERE EpisodeID = " & me.EpisodeID
end sub

    This is functionally identical to the previous definition, but removes the name "MainForm" from the call, thus meaning that if someone decides to change the name of MainForm, or to clone this functionality to another form at some point in the future, this code will not have to be rewritten or modified.