MS Access Filter Subform (DataSheet) with another Subform (List)

Question

I have a Project to be done in MS Access 2016, and stubled across an Issue, that should be easy to resolve, however, I have no clue on how to do it.

The Database I am developing is based on a huge, unfiltered datasheet exported by another database. I have a main form headview in which I placed two subforms listview an detailview. The listviewis sorted by a combobox.

Now to what "should" happen: If you click on an entry of said listview, the detailview shows additional information of the clicked entry.

Both subforms are based on the same datasheet. So I went ahead and tried to match them via primary key entries. However, that didnt work. The detailview subform is still empty. I also tried to write a vba macro for the listview with listview.click() that didnt work either.

Is there a way to connect those two subforms within a main form? If so, how do I do that?

I am greatfull for any response,

Have a nice day -Ninsa

Answer 1

Ok, finally I got the reason for error 2455, it's a timing problem.

When the procedure Form_Current of the listview form is called the first time, the detail subform is not yet bound to the detail subform control, which causes the error.

Possible solutions

Option1: Ignore error 2455

Either add On Error Resume Next in the top of the Form_Current procedure or rewrite it to handle that specific error 2455:

Private Sub Form_Current()
    On Error GoTo Catch

    With Me.Parent.DetailSubformControl.Form
        .Filter = "[ID] = '" & Me.ID.Value & "'"
        .FilterOn = True
    End With

Finally:
    Exit Sub

Catch:
    If Err.Number = 2455 Then Resume Finally
    MsgBox Err.Number & "(" & Err.Description & ") occured.", vbExclamation, "Attention"
    Resume Finally
End Sub

Option2: Control the source objects of the subform controls

Clear the Source Object property of the subform controls in the head form and set them explicit when loading the head form.

That prevents the unlucky timing at all.

So in the head forms load event procedure add this:

Private Sub Form_Load()
    Me.DetailSubformControl.SourceObject = "Table1Detail"
    Me.DatasheetSubformControl.SourceObject = "Table1Datasheet"
End Sub

Option3: Use Link Master/Child Fields

You could use the properties Link Master Fields and Link Child Fields of the detail subform control.

Therefor you have to create a textbox control named ID on the head form and for cosmetic aspects hide it by setting its property Visible to False.

This new control will be bound to the detail subform control: Set the property Link Master Fields and Link Child Fields of the detail subform control on the head form both to ID.

The Form_Current procedure of the listview form then only contains this:

Private Sub Form_Current()
    ' Set the value of the hidden control 'ID' on the head form,
    ' which is bound to the detail subform control, to the selected ID.
    Me.Parent.ID.Value = Me.ID.Value
End Sub