change a subform recordsource on click (1 Viewer)

[Manos39]

Hi,
i would like help to change a subform's recordsource by clicking a command button in my main form.
Read some articles but still cannot achive it

Main form name is adeiesfrm and subform's name is adeiesfrmsub1
adeiesfrmsub1 default recordsource is adeiestbl
and i need changing that recordsource to a query named Qradeiescurrentlastyear by clicking button "Show Current and Previous Year"
here is my tests none works

Private Sub Command64_Click()
'Me.adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Me.adeiesfrmsub1.Form.RecordSource = Qradeiescurrentlastyear
'Forms!adeiesfrm!adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Forms!adeiesfrm!adeiesfrmsub1.Form.Requery
Forms!adeiesfrm.adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Me.adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Me.adeiesfrmsub1.Form.Requery

i provide my database if it is needed

 

[Minty]

What's the subforms container control called?

Bookmark this page: http://access.mvps.org/access/forms/frm0031.htm

 

[Manos39]

What's the subforms container control called?

Bookmark this page: http://access.mvps.org/access/forms/frm0031.htm

It is a button "Show Current and Previous Year" if i undrestood the question

 

[theDBguy]

Looks to me like you can do the same by simply applying a filter to your subform instead of using a separate query for it.

 

[theDBguy]

Is this close to what you want?

 

[Manos39]

Looks to me like you can do the same by simply applying a filter to your subform instead of using a separate query for it.

Yes it is quite close, only it is missing records if opened side by side Qradeiescurrentlastyear and main form with filter applied for the same person
that query has : WHERE (((DateDiff("yyyy",[Enarxi],Date())<=1)=True))
Your solution is fantastic but i would prefer solving that problem as i intended to, so i could use it somewhere in future...

 

[theDBguy]

Yes it is quite close, only it is missing records if opened side by side Qradeiescurrentlastyear and main form with filter applied for the same person
that query has : WHERE (((DateDiff("yyyy",[Enarxi],Date())<=1)=True))
Your solution is fantastic but i would prefer solving that problem as i intended to, so i could use it somewhere in future...

I didn't want to use the same criteria, but I guess I have to. Try it now...

 

[LarryE]

Hi,
i would like help to change a subform's recordsource by clicking a command button in my main form.
Read some articles but still cannot achive it

Main form name is adeiesfrm and subform's name is adeiesfrmsub1
adeiesfrmsub1 default recordsource is adeiestbl
and i need changing that recordsource to a query named Qradeiescurrentlastyear by clicking button "Show Current and Previous Year"
here is my tests none works

Private Sub Command64_Click()
'Me.adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Me.adeiesfrmsub1.Form.RecordSource = Qradeiescurrentlastyear
'Forms!adeiesfrm!adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Forms!adeiesfrm!adeiesfrmsub1.Form.Requery
Forms!adeiesfrm.adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Me.adeiesfrmsub1.Form.RecordSource = "Qradeiescurrentlastyear"
'Me.adeiesfrmsub1.Form.Requery

i provide my database if it is needed

Try:
Forms![adeiesfrm]![adeiesfrmsub1].Form.RecordSource = "Qradeiescurrentlastyear"
Forms![adeiesfrm]![adeiesfrmsub1].Form.Requery

 

[theDBguy]

I didn't want to use the same criteria, but I guess I have to. Try it now...

Okay, compare that to the criteria I really want to use.

 

[Manos39]

Okay, compare that to the criteria I really want to use.

Yes it is correct!! Thank you!!