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

Answer 2

You should handle filtering of the detailview on the Listview_Current event. That event fires as soon as Listview changes records.

You can either set up an event handler for the Listview_Current event on the listview's form module, or use WithEvents in the parent form to listen to that specific event.

If you choose the latter, note that it's required that Listview has a form module, else the events won't fire.

Answer 3

Given that your ID field of the datasource is ID and the detail subform control is named DetailSubformControl, this example works.

Place this code to the Form_Current event of the listview subform (which is fired on every record you move to):

Private Sub Form_Current()
    ' Set a reference to the detail subform control
    With Me.Parent.DetailSubformControl
        ' Set the filter of its contained form to the current ID of the listview.
        ' The "'" are only necessary if it is a text and not a numeric field.
        .Form.Filter = "[ID] = '" & Me.ID.Value & "'"
        .Form.FilterOn = True
    End With
End Sub