Search code examples
sqlms-accessvbasubform

Show records based on same id in three subforms in Access within a form

I know I'm looking for a quick fix and that the main issue is in the database design but for the moment I cannot do anything about it.

So here's my wish:

I have three tables TableA, TableB, TableC all sharing ID as key with referential integrity turned on (de facto it would be one large table that has more than 255 columns which is the limit I have to find a workaround for). What I would like to achieve is to show all records simultaneously as datatable next to each other and have the following behaviours:

  • if I filter in table A, Table B und C should show the same rows
  • Sorting should be also equal and should be done by certain columns in Table A
  • I already managed to have the cursor in the same row on all tables

desired layout

I thought of making a select * from tableB where id in filteredrecordset of tableA or some sort of join on that recordset but did not manage to achieve that.

As a sidenote: there are about 100k records in that database and the performance has to be fast as this view is mainly used for data entry/updates on multiple columns and rows which requires such a flat data structure.

Thanks in advance for your help!

Solution

  • You can use the forms On Filter event to sync up the filters. However, I assume you've bound the subforms directly to the table.

    Because you've bound the subforms directly to table, you can't listen to events. However, I've recently encountered that issue, and have a hacky workaround, but you don't need to use that if your field names and table names are constant. You just need to wrap TableA in a datasheet form.

    Open up TableA, and go to the Create tab, then More Forms -> Datasheet. You now have a datasheet form that captures all fields from TableA. Then, add a module to that datasheet form. You don't need any code in that module.

    Then, instead of binding the first subform to TableA, we bind it to this datasheet form instead.

    Now, on the parent form, we're going to set up an event handler for the filter.

    On the parent form (I'm assuming the name of your subform control for tableA is SubA, for tableB SubB, for TableC SubC):

    Private WithEvents tblAForm As Form 'Declare tblAForm to handle events

Private Sub Form_Load()
    'Initialize event handler, sync initially
    Set tblAForm = Me.Controls("subA").Form
    tblAForm.OnApplyFilter = "[Event Procedure]"
    SyncFilters 'Not needed if you're not persisting filters, which you likely aren't
End Sub

Private Sub tblAForm_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    'Sync filters
    SyncFilters(ApplyType)
End Sub

Private Sub SyncFilters(ApplyType As Integer)
    Dim srcB As String
    Dim srcC As String
    Dim strFilter As String
    Dim strOrder As String
    'If filter or sort are on on TableA, we need to join in TableA for sorting and filtering
    If tblAForm.FilterOn  Or tblAForm.OrderByOn Then
        srcB = "SELECT TableB.* FROM TableB INNER JOIN TableA On TableA.ID = TableB.ID"
        srcC = "SELECT TableC.* FROM TableC INNER JOIN TableA On TableA.ID = TableC.ID"
        'Filter to SQL
        strFilter = " WHERE " & tblAForm.Filter
        'Sort to SQL
        strOrder = " ORDER BY  " & tblAForm.OrderBy
        If tblAForm.FilterOn And tblAForm.Filter & "" <> "" And ApplyType <> 0 Then
            'If the filter is on, add it
            srcB = srcB & strFilter
            srcC = srcC & strFilter
        End If
        If tblAForm.OrderByOn And tblAForm.OrderBy & "" <> "" Then
            'If order by is on, add it
            strB = srcB & strOrder
            srcC = srcC & strOrder
        End If
    Else
        srcB = "SELECT TableB.* FROM TableB"
        srcC = "SELECT TableC.* FROM TableC"
    End If
    If srcB <> Me.SubB.Form.RecordSource Then Me.SubB.Form.RecordSource = srcB
    If srcC <> Me.SubC.Form.RecordSource Then Me.SubC.Form.RecordSource = srcC
End Sub

    Note that you do need some spare fields to allow for filtering and ordering. Any field that's used for that does count towards the maximum of 255 fields. If you might hit that, you could consider splitting the dataset into 4 tables instead of 3