Can I modify a report from a table?

[minchazo]

I'm using Access 2003. I'm using a control button in a table (tblReport) to open a weekly report. The report has to run for four separate sites, all stored in tables together.

I can create a separate query and report for each site by changing this line in my query for each site:

WHERE Site_Name="Site1"

I am adding a comboBox to tblReport so I can capture the site that the user wants to see. Do you know of a way to modify the SQL line in the query from the table before running it? I'm trying to reduce 4 queries and 4 reports to 1 query and 1 report.

Thanks,

Minchazo

[nico5038]

I'm using Access 2003. I'm using a control button in a table (tblReport) to open a weekly report. The report has to run for four separate sites, all stored in tables together.

I can create a separate query and report for each site by changing this line in my query for each site:

WHERE Site_Name="Site1"

I am adding a comboBox to tblReport so I can capture the site that the user wants to see. Do you know of a way to modify the SQL line in the query from the table before running it? I'm trying to reduce 4 queries and 4 reports to 1 query and 1 report.

Thanks,

Minchazo

The general approach I use is to create a reportquery without limitations, like your:

WHERE Site_Name="Site1"

When the report is triggered by a button I just pass the needed filtering like:

DoCmd.OpenReport "ptName", acViewPreview, , "Site_Name='" & Me.cmbSite & "'"

The site can be selected from a combo using a GroupBy query to select the distinct sites from the table.

Getting the idea ?

Nic;o)

[minchazo]

Wahoo! That works perfectly, thanks!

[missinglinq]

"Access 2003. I'm using a control button in a table (tblReport)"

Just out of curiosity, how do you add a command button to a table?

Linq ;0)>

[minchazo]

Just out of curiosity, how do you add a command button to a table?

Errr, oops? I was using a form instead of a table. I even named the form as if it was a table, which I had to fix in the database (frmReport instead of tblReport).