OpenArgs to Sub Report

Bob Bedell · January 19th, 2005, 12:50 AM

What happens if you open the sub report in design view, paste the filter string (e.g. [EPRproid] In ('1','2')
) into its Filter Property, set Filter On to Yes, and run the sub report as a stand-alone report? Is the report filtered correctly?

I was also wondering about your table structure. I'm assuming one employee can work on several different projects and that each project can have several different employees assigned to it. If you're attempting to model that type of many-to-many relationship, it seems that it would be much easier to dispense with the sub-report altogether, and simply generate a report based on a query that reflects the many-to-many relationship involved (using the reports WhereCondition to filter for individual employees if you like).

What I have in mind is a table schema that looks like:

Schema Key:

PK = Primary Key
FK = Foreign Key
CPK = Composite Primary Key (Primary Key consisting of more than one field)

tblEmployees
-EmployeeID (PK) (AutoNumber)
-EmployeeFirstName (Text)
-EmployeeLastName (Text)

tblProjects
-ProjectID (PK) (AutoNumber)
-ProjectName (Text)

tblEmployeeProjects (Linking Table)
-EmployeeID (CPK)(FK - tblEmployees)(Number)
-ProjectID (CPK)(FK - tblProjects)(Number)

Sample Data:

tblEmplyees

Code:

"EmployeeID" "EmployeeFirstName"     "EmployeeLastName"
1            "John"                  "Doe"
2            "Jane"                  "Doe"
3            "Jed"                   "Doe"

tblProjects

Code:

"ProjectID"     "ProjectName"
1               "Project1"
2               "Project2"
3               "Project3"

tblEmployeeProjects

Code:

"EmployeeID"    "ProjectID"
1                1
1                2
1                3
2                1
2                2
3                1

Using this schema, which is the standard method of implementing a many-to-many relationship in relational databases, you could write a query like:

qryEmployeeProjects

Code:

SELECT e.EmployeeID, EmployeeFirstName, EmployeeLastName, p.ProjectID, ProjectName
FROM   tblEmployees e, tblProjects p, tblEmployeeProjects ep  
WHERE  e.EmployeeID = ep.EmployeeID
AND    p.ProjectID = ep.ProjectID

or the nasty looking Access genenerated version of the same query (INNER JOIN syntax should be outlawed):

Code:

SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeFirstName, tblEmployees.EmployeeLastName, 
       tblProjects.ProjectID, tblProjects.ProjectName
FROM tblProjects 
INNER JOIN (tblEmployees INNER JOIN tblEmployeeProjects 
ON tblEmployees.EmployeeID = tblEmployeeProjects.EmployeeID) 
ON tblProjects.ProjectID = tblEmployeeProjects.ProjectID;

and generate a single report which uses this query as its record source, like:

EmployeeProjectReport

Code:

EmployeeID      EmployeeFirstName       EmployeeLastName        ProjectID       ProjectName

1               John                    Doe  
                                                                1               Project1
                                                                2               Project2 
                                                                3               Project3
2               Jane                    Doe 
                                                                1               Project1 
                                                                2               Project2
3               Jed                     Doe 
                                                                1               Project1

The Access report wizard will kick out this format for you is you give it the query above.

Just a thought anyway. Your sub-report filtering approach seems like a bit of a work-around for what appears to be a table schema design issue. I'm not sure I can offer any further codeing suggestions without a better handle on how you set up your tables and what your object record sources are.

HTH,

Bob

Bob Bedell · January 19th, 2005, 05:54 AM

Anyway, my point isn't about the formatting, and the example is pretty simplistic, and I don't want to make a lot of assumptions about what you want to do. Using a subreport is fine, it just seems that the employee data in the parent report and the project data in the subreport could be easily linked by an EmployeeID field in the record sourse of each based on a M:M relationship.

mwn1218 · January 19th, 2005, 12:28 PM

I agree with you on that. That is how I have the tables set up, and the only reason I am using a sub-report is for formatting issues (this is coming from management):

Employee Name: name

other stuffL: other stuff

[u]Projects : 1</u>
                  2
                  3

But when i put all the information into one report

Employee Name: name

other stuffL: other stuff

[u]Projects : </u>
[u] 1</u>
                  2
                  3

They want the projects title and the first project to show up on the same line (thats not my choice) and I am not sure how to write code to only show the Projects label for the first project (the project info was in the detailed section) So if you could show me an example of that, that would help alot.

When i run the sub-report alone the project works (i have done this by opening it from the command button and just opening the sub-report) and that is why i dont understand when i try to send teh arguement from the main report to the sub it doesn't work.

I am new at this also that is why i have been asking so many questions. Just grasuated and still learning. But i FINALLY understand formating is not a problem .

Thanks again for all the help.. you have really taught me alot.