need help with drop down to select report 1

[pduncan]

I have a system that has 8 reports - each report has it's own query. Aside from the data source, the reports are the same except for a label which contains the reports title. The queries are all the same as well, except for 1 field. When I have a change in the report, I currently have to change all 8 queries and all 8 reports. I would like to just have 1 report in which the query source and report title are populated by a dropdown. I guess I would need a second drop down to pull in the specific query information.

Can someone help me get started? My crummy installation of Access didn't include the help files (what was IT thinking?) and after looking over this site for a few hours, I still cant locate any examples similar to what I am trying to do.
Thanks


PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[dhookom]

It is very rare that you would need to change the record source of a report. Almost 99% of the situations require only that the returned records are filtered different based on some user interaction. For instance a different date range or InvoiceID or EmployeeID can all be created using the same report and same query. How are your queries different?

Duane
MS Access MVP

 

[pduncan]

this is a split application. Basically, the reports and queries are in the front end. There are 8 back ends. Each mdb represents a seperate company. This is why I had seperate queries, as I was not sure to have 1 report/query connect to objects in other databases.
Each query pulls back the same data from it's respective db. The thing that changes is business cycle.
Currently, I have it set up that when a user updates their links to connect to a particular company. A form has command buttons that show the available reports. I hide the buttons for the databases they are not connected to using a bunch of case and visible =t/f. If it were not for the seperate backends, I think I could figure this out on my own.
Back to your question, each company has a couple of queries each - the parameter that changes between them is a field called ActivityID which is 1 of 4 numbers.

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[dhookom]

You can use code in the On Open event of the report that would set the recordsource property of the report. The method that I have used in the past is to change the sql of a saved query prior to opening the report. For instance, if your report is based on "qselMyData" and you wanted this query to pull data from either "qselCompA" or "qselCompB":

Dim strSQL As String
If Me.optCompany = 1
Currentdb.QueryDefs("qselMyData&quot.SQL = "SELECT * FROM qselCompA;"
Else
Currentdb.QueryDefs("qselMyData&quot.SQL = "SELECT * FROM qselCompB;"
End If
DoCmd.OpenReport "rptMyReport",acViewPreview



Duane
MS Access MVP

 

[pduncan]

I am lost -
thanks for your help but can we back up a step?
Lets say that I have 4 queries that are the same except for they each are looking for a different ActivityID.
The values are 1,2,3,4
I want to get rid of them and just use one query that has the ActivityID field updateable (changeable?)

How would I put a combo or list box on a new form that had these vales above, and then would populate a single query, and changing ActivityID to the selected field. - THEN - opan a report based on that query.

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[dhookom]

You could create a form "frmSetCriteria" with a combo box "cboActivity" that has a row source of 1, 2, 3, 4. You could create one query and set the criteria to:
Forms!frmSetCriteria!cboActivity
And alternative that I prefer is to open a report using a where clause.
DoCmd.OpenReport "rptActivities", acViewPreview, , "[ActivityID]=" & Me.cboActivity


Duane
MS Access MVP

 

[pduncan]

Duane - That worked perfectly! Thanks very much for your help.

PDUNCAN - MEMPHIS, TN

When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.