Solved open mainform with vba using where condition to filter the subform inside the mainform (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:55
Joined
May 7, 2009
Messages
16,125
add an Unbound textbox to your "second form".
then add Link Master/Child fields to the subform.
set the unbound textbox from the Original form.
see this demo.
 

Attachments

  • filterAnotherForm.accdb
    460 KB · Views: 39

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:55
Joined
Oct 29, 2018
Messages
18,792
oooh, my mistake, i though openArgs is same like filter parameter, after searching online now, I found it passes values between forms
and that's exactly what I was searching for

so I have put code to get the value of the field "FollowupYear" as in this code
Code:
    DoCmd.OpenForm "frm_FollowUp_New", acNormal, , , , , Int(Me!FollowupYear)

then on load form "frm_FollowUp_New" I've used @The_Doc_Man filter code as:
C++:
Private Sub Form_Load()
    Me.subfrm.Form.Filter = "FollowupYear=openArgs"
    Me.subfrm.Form.FilterOn = True
End Sub

and it works exactly like what I want

Thanks @theDBguy , @The_Doc_Man and @Gasman

I've learned something new today because of all your insisting (y)(y)(y)

and also I've used another way to get the same results, maybe someone will find it helpfull
C++:
Private Sub btnEdit_Click()
    DoCmd.OpenForm "frm_FollowUp_New", acNormal
    Forms!frm_followup_new!subfrm.Form.Filter = "FollowupYear= " & Me.FollowupYear
End Sub

So, no need to use on load event on the opened form
Hi. Congratulations! Glad to hear you got that part sorted out. There are certainly other ways to accomplish the same thing. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:55
Joined
Feb 19, 2002
Messages
35,925
this button opens another form"mainform" that has a subform
This is clearly causing the problem and all the confusion since we are 23 posts into answering what should be a simple question. Generally we do NOT use subforms on unbound forms and that seems to be what you are doing. If the subform on the popup form is single record, WHY is it in a subform?

The simplest solution is of course to not use a subform. However, if you are welded to the idea of the subform, change the RecordSource query of the popup's subform to reference the calling form/subform.

Where Somefield = Forms!firstMainForm!FirstSubform!ControlName

or you might need this syntax. I can't keep track of which works where

Where Somefield = Forms!firstMainForm!FirstSubform.Form!ControlName
 

alvingenius

IT Specialist
Local time
Today, 08:55
Joined
Jul 10, 2016
Messages
168
This is clearly causing the problem and all the confusion since we are 23 posts into answering what should be a simple question. Generally we do NOT use subforms on unbound forms and that seems to be what you are doing. If the subform on the popup form is single record, WHY is it in a subform?

The simplest solution is of course to not use a subform. However, if you are welded to the idea of the subform, change the RecordSource query of the popup's subform to reference the calling form/subform.

Where Somefield = Forms!firstMainForm!FirstSubform!ControlName

or you might need this syntax. I can't keep track of which works where

Where Somefield = Forms!firstMainForm!FirstSubform.Form!ControlName

Thanks for your reply,
Who said anything about me using subforms on unbound forms ?
i have to use subforms in bound forms since the subform act as a junction table

and i've already edited the main thread with the solution post.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:55
Joined
Feb 19, 2002
Messages
35,925
Who said anything about me using subforms on unbound forms ?
I think you did.
I've a main form with subform inside it
main form have only a combobox to filter the subfrm

But as long as you have a solution. That's fine.
 

Users who are viewing this thread

Top Bottom