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

Question

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

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!

Wouldn't be this much faster to have a cascading setup? B & C shows rows related to the "selected row" in Table A? that would be a simplest Access solution? — Krish, Aug 21 '18 at 10:31
Hi Krish Km, unfortunately your solution doesn't work for me because i need each logical row (i. e. the row consisting of one record from each table) to be in one line in order to enable fast/bulk editing over multiple rows and columns. thanks for the suggestion, anyway! — Jan, Aug 22 '18 at 05:52
Wouldn't it be easier to export the data to EXCEL and edit there, then reimport? If https://www.sqlservercentral.com/Forums/Topic101090-23-1.aspx is correct, you can link all fields at once with an ADO connection to SQL Server. — ComputerVersteher, Aug 23 '18 at 14:58
Do you want to allow users to edit more than 255 columns at once? It seems like the simplest solution would be to just create a query joining the three tables and SELECT only the columns you want the users to edit. I can't imagine a usable interface with over 255 columns but maybe you are dealing with very specialized circumstances... — mwolfe02, Aug 24 '18 at 14:24
hi mwolfe02, it is indeed the requirement to edit all fields at once or at least one after the other based on a selection that depends on multiple columns. we're looking into this issue on how to change processes but meanwhile a workaround would be more time for a better solution — Jan, Aug 25 '18 at 19:45

Erik A · Accepted Answer · 2018-08-27T08:18:13.650

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

Wouldn't you have to add the column(s) from TableA that are used for filtering / sorting to the SELECT for subforms B+C? — Andre, Aug 20 '18 at 22:05
@Andre Sorry, after some more testing, you were right. I reworked my approach to adjust the recordsource instead of using filters, that does work. — Erik A, Aug 23 '18 at 13:55
hey @ErikvonAsmuth: , works nicely in general. currently I have two open issues: 1) if I remove the filter from subform A, the applyFilter doesn't produce the desired result but keeps subforms B and C filtered. Seems to be an order of events issue. 2) I had to enter an empty Private Sub Form_ApplyFilter in the macro for Form_subA in order to make tblAForm_ApplyFilter fire. 2) is just a small edit to make your answer more helpful but for 1) I haven't found any good solutions. any advice? — Jan, Aug 27 '18 at 07:59
@Jan See the edits I've made. The second one is a simple mistake: I've used `OnFilter` while I should've used `OnApplyFilter` on load. No code needed in the form. The second one is a bit weird, I tested it with ordering but not filtering, but we can use the `ApplyType` to detect when the filter is removed. Thanks for the edits. — Erik A, Aug 27 '18 at 08:19
@ErikvonAsmuth ApplyType doesn't work for autofilters (at least for me). Applytype for me is always 1. I solved it with a boolean variable to indicate the filter status. — Jan, Aug 27 '18 at 09:30

score 2 · Answer 2 · answered Aug 20 '18 at 19:51

Consider using RecordSourceClone property and a temp table of IDs that runs the following routine:

On Exit of any subform after filtering, IDs are iteratively appended to a temp table.
The other two subforms have their RecordSources filtered to temp table IDs.
Reset button removes all filters in order to run different criteria.

VBA

Option Compare Database
Option Explicit

' RESET ALL SUBFORMS
Private Sub RESET_Click()
    Me.Controls("frm_TableA").Form.RecordSource = "TableA"
    Me.Controls("frm_TableB").Form.RecordSource = "TableB"
    Me.Controls("frm_TableC").Form.RecordSource = "TableC"
End Sub

Private Sub frm_TableA_Exit(Cancel As Integer)
    Call RunFilters("frm_TableA", "frm_TableB", "frm_TableC")
End Sub

Private Sub frm_TableB_Exit(Cancel As Integer)
    Call RunFilters("frm_TableB", "frm_TableA", "frm_TableC")
End Sub

Private Sub frm_TableC_Exit(Cancel As Integer)
    Call RunFilters("frm_TableC", "frm_TableA", "frm_TableB")
End Sub

Function RunFilters(curr_frm As String, frm1 As String, frm2 As String)
On Error GoTo ErrHandler

    Dim rst As Recordset, tmp As Recordset

    ' DELETE PREVIOUS TEMP
    CurrentDb.Execute "DELETE FROM IDTempTable", dbFailOnError
    Set tmp = CurrentDb.OpenRecordset("IDTempTable")

    ' RETRIEVE FILTERED FORM RECORDSOURCE
    Set rst = Me.Controls(curr_frm).Form.RecordsetClone

    ' ITERATIVELY ADD IDs
    Do While Not rst.EOF
        With tmp
            .AddNew
                !ID = rst![ID]
            .Update
            rst.MoveNext
        End With
    Loop

    tmp.Close: rst.Close
    Set tmp = Nothing: Set rst = Nothing

    ' FILTER OTHER FORMS
    Me.Controls(frm1).Form.RecordSource = "SELECT * FROM " & Replace(frm1, "frm_", "") & _
                                            &  " WHERE [ID] IN (SELECT ID FROM IDTempTable)"
    Me.Controls(frm2).Form.RecordSource = "SELECT * FROM " & Replace(frm2, "frm_", "") & _
                                            & " WHERE [ID] IN (SELECT ID FROM IDTempTable)"        
ExitHandler:
    Exit Function

ErrHandler:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUN-TIME ERROR"
    Resume ExitHandler
End Function

This will behave glitchy. Ordering won't sync up, and after entering another subform, you won't be able to reset filters. Also, I doubt performance will be anywhere close to reasonable with the specified 100K records. The advantage over my approach would be that you could filter on multiple subforms, but the question only wants to filter on the first one. — Erik A, Aug 20 '18 at 20:17
I should have noted that I have tested this on three subforms of three different tables. — Parfait, Aug 20 '18 at 20:23

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

2 Answers2