• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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.

  • 3
1 Solution
In the report's 'On Activate' event try:

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

Not sure if you really need the Me. but it doesn't hurt either. :-)
jberv534Author Commented:
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.

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....
Actually for the second method, I'm assuming that you are linking your report's subreport to the main report using the ProjectID as the Master and Child link fields(see subreport properties).

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now