change records source and run query

[robina]

I have a tabbed form in a navigation form with a chart on it. The records source of the chart is a query. The query runs when you click the tab and takes a long time. I changed the Row Source of chart to "" and that eliminates the query running on form load. I've read many posts on changing that row source when a command button is clicked. I tried

    Me!Suspend_Trending_Dashboard![chart].RecordSource   = suspend_trend_CHART

in the onclick event of the command button. This doesn't work. I've tried many variations of the syntax. I don't know if I have to tell the query to run after the row source is changed.

On a side note, the query criteria is based on beginning and end dates entered into text boxes on the form. This all works if the query loads when the form is opened.
I'm at the end. I appreciate all help.

Robin

 

[pbaldy]

It's a string argument, so try

Me!Suspend_Trending_Dashboard![chart].RecordSource = "suspend_trend_CHART"

Setting the source will run the query.

 

[robina]

Thank you. I didn't have to reference chart. This is what I used and it works great:
Forms!Naviagationfrm!Suspend_Trending_Dashboard.Form.RecordSource = "suspend_trend_CHART"

My problem now is it runs great when i put in begin and end dates in the text boxes then click search, but if i change the dates and click search again it does nothing.

Any suggestions?

 

[Kowalski]

You will have to recreate the query's sql with code and pass in the dates as a where clause.

Currentdb.QueryDefs("suspend_trend_CHART").SQL = "SELECT xx FROM xx WHERE MyDate Between #" me.StartDate & "# AND #" me.EndDate & "#"

 

[robina]

which event would this go into? Also, will this trigger after changing the dates again and then clicking the search command button again?

 

[pbaldy]

I would try requerying the form.

 

[Kowalski]

which event would this go into? Also, will this trigger after changing the dates again and then clicking the search command button again?

Put it in the On_Click event of the Search button.