Using a subform within a form and displaying the results in the subform filtered by textbox value

[mp3909]

Hi,



What am I trying to achieve
I have a main form that contains a subform and I want the contents displayed inside the subform to be filtered by the value of a textbox in the main form.
Main form = Form1
Sub form = Subform1
TextBox = Text1


See picture below



Currently, my subform is not displaying anything, just comes up empty despite setting the parts I have marked in yellow in the photo below i.e. filtering the tblMovements by case id = value in text box 1

[moke123]

Can you just set the Master/Child links in the subform control using CaseID?

[Welshgasman]

Just do what Moke123 advises.

[mp3909]

Can you just set the Master/Child links in the subform control using CaseID?

I tried your approach but still not working.


In tblCases I have CaseID = 234




In tblMovements there are records for CaseID = 234




However, when I go to my main form (i.e. Form 1) and select that client who has CaseID = 234 the Audit Trail of Client comes back empty (i.e. the subform appears blank and weirdly just shows CaseID =1 regardless of which client is selected).




I would have expected it to show the records when you filter tblMovements for CaseID = 234


Here is the form properties for my subform (i.e. frmCaseDetail) and you can see it is bounded to tblMovements









And my main form is bounded to tblCases.
The CaseID field in my tblCases is of the type AutoNumber and it set to Primary Key.
The CaseID field in my tblMovements is of the type Number and it is not a Primary Key.
I have defined a "One-to-Many" relationship between the two tables.

Why is this not working then? I have I missed something?


Thank You

[Welshgasman]

Upload a sample db.
If you need to hide the data use this

[mp3909]

Following on from my last message, I just want to make it clear that that CaseID textbox inside my main form derives it value from the following vba code:






So when user selects a client from the List it will then auto populate the CaseID field by running that VBA code.

Will that have something to do with my subform not working?

[Welshgasman]

I would assume so, as you do not appear to have CaseID actually on the mainform?
Also give your controls meaningful names. Get in the habit of doing that.

You need to use that control as the Master link.
Not sure if the source for that should be caseID, try it anyway.

[mp3909]

I would assume so, as you do not appear to have CaseID actually on the mainform?

Sorry, what do you mean by this as I don't quite understand. I do have a CaseID textbox that gets automatically populated by vba code after user has selected a value in the list box

[mp3909]

Upload a sample db.
If you need to hide the data use this

I have attached my db as I am stuck.
Please let me know if I have not made it clear on what I am trying to achieve.
Thank you.

[Welshgasman]

Use your control name for the CaseID on the mainform as Master Link value.
I renamed yours to txtCaseID.

[mp3909]

splendid!
Thank you so much Welshgasman

[Welshgasman]

You're welcome.
Have a read of these links for naming conventions.
https://www.google.com/search?q=acce...hrome&ie=UTF-8

[moke123]

I think your error is the after update event of the listbox.

Your main form (The parent of the listbox) is bound to tblCases.
The listbox is also bound to tblCases.

If you sync them then the Master/Child links will work correctly. By using the textbox, the main form is staying on CaseID 1 while your subform is moving to another CaseID.

In the after update of your listbox use the findfirst method to sync them.

Private Sub List15_AfterUpdate()

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone


    With rs
        .FindFirst "CaseID = " & Me.List15


        If Not .NoMatch Then
            Me.Bookmark = rs.Bookmark
        Else
            ' a messagebox if you like
        End If
        
    End With


End Sub