Generate a report using current record data from two subforms

[Platinum]

Hello everyone.



A while back I created a skills matrix database that compares an employee’s skills based on their current job title with a proposed job title. The conditional formatting on subfrmPropReq indicates whether an employee lacks the proficiency of the skill, needs improvement, or doesn’t have the skill at all (by comparing it to sbufrmEmpSkills). What I am trying to do now is to create a report that extracts all of the current information from both subforms and puts it on a report. This report would report list all of the employees skills (sbufrmEmpSkills) in the top half of the report, and the bottom half would show only the skills that the employee is lacking based on the job title they are being compared to (subfrmPropReq). However, I’m having difficulty figuring out how to do this.


One of the solutions I thought would work is generating a report by including both subforms as subreports on the main report – that doesn't seem to work. I know there's a feature to include subforms on a report, but because subfrmPropReq is blank by default and is programmed to a form's combo box it's not showing any information. Also it gives me an error saying “The link you selected can’t be used. This link was created based on relationships between source tables. To use this link you must add the missing fields to the form or report’s record sources.”

Then I thought about plugging in a print command button to just print out the record – that doesn’t seem to work either.

The file to the database is located here for reference: https://www.dropbox.com/s/kpdrhugfc4...opy.accdb?dl=0

I’m really struggling here as I am limited in my Access knowledge and abilities. Any help would be greatly appreciated!

[ranman256]

Build your report to include the sub reports,then open the report on the master record....
docmd.openreport "report",acpreview,"[id]=" & txtID

[ssanfu]

Something like this??????

[Platinum]

@ssanfu: Yes that is perfect! Holy cow I can't believe it was that easy! Thank you so much! I'm really trying to learn a little more about Access as I use it. I was able to build the formulas and the queries and figure out some of the VB stuff, but for some reason I hit a brick wall when I was trying to do this. Also I like the idea about using the criteria to filter the 'Qualified' and 'ReqImp' fields on the queries -- genius! I'll apply that to the subform as well so it doesn't repeat redundant information. Thanks again!

@ranman256: It looks like ssanfu was able to apply what you said to the database. I understand now what needed to be done: I just didn't know how to get there. Thanks anyways though.

[ssanfu]

You're welcome and good luck! Glad it was able to get you moving in the right direction.

Ready to mark this solved?
(see https://www.accessforums.net/showthread.php?t=1828)

[Platinum]

@ssanfu: I noticed that whenever an employee's skill has the "Needs Improvement" box checkmarked, the database does two things: 1) subfrmPropReq automatically shows that information on the form by default when switching records without focusing on the "Proposed Job Title" combo box first, and 2) both the subform & the report duplicates entries and duplicates them with unrelated proficiencies. Is there something else I need to do to fix this? I tried setting the query on the form to "unique values" and "unique records", but that doesn't seem to do anything. I also tried fudging around with the query criteria, but that isn't doing anything either.

I attached screenshots to show what the problems I'm describing look like.