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

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
Hello

I've a main form with subform inside it
main form have only a combobox to filter the subfrm

and i wanna open this main form from a button on another form using vba code:
Code:
DoCmd.OpenForm "mainform", acNormal, , "xYear= "& me.txtYear

but i wanna use where condition too to filter the subform inside this mainform while openeing the mainform
the above code won't work of course because it will filter the mainform !, So, how to make it filter the subform inside the mainform

i hope you get it

--
Edit
Solution is here
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
18,792
You can use the main form's Load event to filter the subform by passing the filter info in the OpenArgs parameter.
 

Ranman256

Well-known member
Local time
Today, 01:34
Joined
Apr 9, 2015
Messages
4,094
DoCmd.OpenForm "mainform", acNormal, , "[xYear]=" & me.combo
 

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
You can use the main form's Load event to filter the subform by passing the filter info in the OpenArgs parameter.

thanks @theDBguy for your reply
i can't do it that way
because the button is in continuous form, so i need to use the where condition on the button to get the value from a control on this continuous form
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
18,792
thanks @theDBguy for your reply
i can't do it that way
because the button is in continuous form, so i need to use the where condition on the button to get the value from a control on this continuous form
Okay, just to be clear, you have a continuous form with a button to open a separate form, correct? So, when you click on the button and it opens the other form and then clicks on a different button, is the other form still open?
 

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
Okay, just to be clear, you have a continuous form with a button to open a separate form, correct? So, when you click on the button and it opens the other form and then clicks on a different button, is the other form still open?

I've a continuous form, with a button on every row record, to edit this record
this button opens another form"mainform" that has a subform, and I need this form opened filtered with same value on the continues form record
and the continues form will close
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
18,792
I've a continuous form, with a button on every row record, to edit this record
this button opens another form"mainform" that has a subform, and I need this form opened with same value on the continues form record
We got that part. I am trying to understand why you said the OpenArgs approach won't work. So, I am trying to clarify if you click the button on the first record that then opens the edit form and then click on the button on the second record to open the same edit form, will that form have closed first (it was displaying the data related to the first record)?
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:34
Joined
Sep 21, 2011
Messages
10,342
Link combo and subform, then set the combo with the openargs value?
 

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
We got that part.

We got that part. I am trying to understand why you said the OpenArgs approach won't work. So, I am trying to clarify if you click the button on the first record that then opens the edit form, this edit form has a subform, and the data i wanna edit on the subform

there's only one button here!
consider it a button on continuous form that opens a form to a specific record, ( my issue here, that this specific record is in a subform inside the main form
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
18,792
We got that part. I am trying to understand why you said the OpenArgs approach won't work. So, I am trying to clarify if you click the button on the first record that then opens the edit form, this edit form has a subform, and the data i wanna edit on the subform

there's only one button here!
consider it a button on continuous form that opens a form to a specific record, ( my issue here, that this specific record is in a subform inside the main form
Hi. I don't see any problems. However, that's just because I have no idea what you're looking at. Can you post a screenshot of your form? If it's the same as any other normal continuous form, then I think the OpenArgs approach should work. Other approaches may also be applicable, not just the OpenArgs method. Seeing what you got may help clarify things better.
 

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
Hi. I don't see any problems. However, that's just because I have no idea what you're looking at. Can you post a screenshot of your form? If it's the same as any other normal continuous form, then I think the OpenArgs approach should work. Other approaches may also be applicable, not just the OpenArgs method. Seeing what you got may help clarify things better.

that's the continues form, with the "Edit" Button
1640030569373.png


so, when this button clicked
i need it to open this form
1640030974121.png



and filter this subform, with the same year i clicked on the button on the continues form
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
18,792

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:34
Joined
Feb 28, 2001
Messages
22,555
Let's take the other approach since the (excellent) advice you have been given doesn't seem to work for you.

The subform is in a subform control. From the main, perhaps in the Form_Load routine, use

Code:
Me.subformcontrol.Form.Filter = "... some filter string..."
Me.subformcontrol.Form.FilterOn = True

Then drop the WHERE and use the rest of the WHERE clause as your filter.
 

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
Hi. Thanks. I still don't see how using the OpenArgs won't work. Have you given it a try?

can you please give me the code to try using openArgs that will pass the filter that in the specific record on another continuous form ?
 
Last edited:

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
Let's take the other approach since the (excellent) advice you have been given doesn't seem to work for you.

The subform is in a subform control. From the main, perhaps in the Form_Load routine, use

Code:
Me.subformcontrol.Form.Filter = "... some filter string..."
Me.subformcontrol.Form.FilterOn = True

Then drop the WHERE and use the rest of the WHERE clause as your filter.

@The_Doc_Man thanks for your reply
when you telling use the filter on the on load event, it will work if the filter is based on a string I will put
Code:
Me.subformcontrol.Form.Filter = "... some filter string..."

I don't have the filter value, the value of the filter is on the continuous form!
so how to recall a specific record as a filter string from another opened continuous form?
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:34
Joined
Sep 21, 2011
Messages
10,342
@The_Doc_Man thanks for your reply
when you telling use the filter on the on load event, it will work if the filter is based on a string I will put
Code:
Me.subformcontrol.Form.Filter = "... some filter string..."

I don't have the filter value, the value of the filter is on the continuous form!
so how to recall a specific record as a filter string from another opened continuous form?
You do have the value of the filter. You need to pass it to the other form. That is what people are telling you when suggesting to use OpenArgs. ?
 

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
You do have the value of the filter. You need to pass it to the other form. That is what people are telling you when suggesting to use OpenArgs. ?
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
 
Last edited:

alvingenius

IT Specialist
Local time
Today, 07:34
Joined
Jul 10, 2016
Messages
168
I'm gonna tell you my other try to make it work without OpenArgs and failed on it, and I don't know why it failed, and I hope you telling me

So, I've thought of using TempVars and filtering the subform using it

so on the button i've put this :

C++:
Private Sub btnEdit_Click()
    Dim TmpYear As TempVars
    TempVars!TmpYear = Me.FollowupYear
   
    DoCmd.OpenForm "frm_FollowUp_New", acNormal

End Sub

and then when the form opens I'll use the on load event to filter the subform using the tempvars value

but when I click on the button I got this error
1640076209837.png


Why ?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:34
Joined
Sep 21, 2011
Messages
10,342
With TempVars you have to use the Value property.
 
Last edited:

Users who are viewing this thread

Top Bottom