asked on

Change Query SQL in VBA

I am using a subreport to display a list of all tasks based on a project ID. I would like to use a list box to allow show selected tasks (based on a field in the list) by using .requery

I would like to change the query that is the source of the subreport.

subreport name is "subProjectTasks"
query name is "ProjectTasks Query"

Any help, as always, is much appreciated.

Jeff

Arji

In the report's 'On Activate' event try:

[subProjectTasks].[Report].RecordSource = "ProjectTasks Query"
or
Me.[subProjectTasks].[Report].RecordSource = "ProjectTasks Query"

Not sure if you really need the Me. but it doesn't hurt either. :-)

jberv534

ASKER

Arji - thanks for the reponse. I did not make myself clear about something:

I want to change the sql statement that ProjectTasks Query uses by adding a WHERE statement.

Jeff

Arji

The Column(0) assumes that in your list, column 0 is the project ID. When you set a RecordSource, it works the same as a requery.

[subProjectTasks].[Report].RecordSource = "select * from ProjectTasks where [project ID]=" & [YourList].Column(0)

Also you can open a report with the criteria in the DoCmd method. Assuming your report's record source is "ProjectTasks":

DoCmd.OpenReport YourReport, acViewPreview, , "[project ID]=" & [YourList].Column(0)

I think this method is better in that you can set criteria dynamically when you open the report

Hope I understood what you wanted....

ASKER CERTIFIED SOLUTION

Arji

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial