Solved Filter all identical foreigen keys in subform (1 Viewer)

Luan

New member
Local time
Tomorrow, 06:55
Joined
Aug 4, 2021
Messages
4
Hi everybody,
I am having a question about filtering in subform, I am not sure if the thread title is correct. Hope that you can help me solve this problem.
I have an access file to input and show some product information (as the first image). After double clicking a row in subform Information (SF_Info), below tabs (contain other subforms) will change. Everything works fine if 1 row in SF_Info link to 1 row in each below subform. But when I added a new subform LabTest (SF_LabTest), 1 row in SF_Info link to many rows in SF_LabTest, Access showed "Run-time error 2465: Microsoft Access can't find the field in 'SF_Labtest' reffered in your expression."
I chose SF_LabTest default view as Continuous form in order to show many results linked to a product information.

Please note that:
In table Info (related to subform Info), the primary key is InfoID
In other tables (related to other subforms), InfoID is the foreign key

1. Home screen.png


This is my VBA code used to filter subform
Code:
Private Sub Customer_DblClick(Cancel As Integer)
    'These lines work perfectly'
    Forms("F_Main")("SF_Material").Form.Filter = "[InfoID]= " & Me.InfoID.Value
    Forms("F_Main")("SF_Material").Form.FilterOn = True
    Forms("F_Main")("SF_Print").Form.Filter = "[InfoID]= " & Me.InfoID.Value
    Forms("F_Main")("SF_Print").Form.FilterOn = True
    .....
    
    'Filter in SF_Labtest does not work'
    Forms("F_Main")("SF_LabTest").Form.Filter = "[InfoID]= " & Me.InfoID.Value
    Forms("F_Main")("SF_LabTest").Form.FilterOn = True
End sub

Thanks for reading my question.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:55
Joined
May 7, 2009
Messages
19,169
the easiest method of "filtering" other subform is by Adding
Master/Child Field Links on each subform.
therefore, you can discard your code of filtering.

on design view of your form,
add an Unbound textbox (txtInfoID) on the "mainform"
the ControlSource of the unbound textbox is:

=[theProductSubformNme].[InfoID]

on each subform on the Tab control,

add
Master Link Fields: [txtInfoID]
Child Link Fields: [InfoID]
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:55
Joined
May 21, 2018
Messages
8,463
I would also do it the way @arnelgp does. But, I would check the real name of your SF_LabTest sub form control. It probably is not the same way as your subform that resides in the subform control. I bet is is something like "Child01" especially if you copied and pasted or added the control then added the source object.
 

Luan

New member
Local time
Tomorrow, 06:55
Joined
Aug 4, 2021
Messages
4
Thanks @arnelgp and @MajP
Is there another way to filter by clicking the SF_Info? I just want to use the the SF_Info because it is easy to check.

@MajP I am pretty sure that SF_LabTest is its real name, I sent some picture about it.
 

Attachments

  • SF_LabTest 2.png
    SF_LabTest 2.png
    17.3 KB · Views: 420
  • SF_LabTest.png
    SF_LabTest.png
    175.5 KB · Views: 245

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:55
Joined
May 21, 2018
Messages
8,463
That appears to be the subform not the subform control. A sub form sits inside the subform control.
 

Luan

New member
Local time
Tomorrow, 06:55
Joined
Aug 4, 2021
Messages
4
That appears to be the subform not the subform control. A sub form sits inside the subform control.
It is difficult to explain. Please view my file in attached file Sample.accdb in *zip file.
 

Attachments

  • Sample.zip
    2.9 MB · Views: 478

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:55
Joined
May 7, 2009
Messages
19,169
it doesn't occur to me.
 

Attachments

  • Sample (2).zip
    3 MB · Views: 484

Users who are viewing this thread

Top Bottom