Filter Report with 3 subreports via Combobox

[mcupito]

Access 2007

I have a feeling there's probably 3-4 ways to do this (Macro, Me.Filter, Like " " , Link Child/Master fields, but I can't figure it out.

I have a Combobox (AstNameCbx) which I am trying to use to filter a report and the 3 subreports that lie within the main report. The fields for this combobox are EmployeeID, AstFirstName, AstLastName. Here is the Record Source for that Combobox:

SELECT [AssociateTbl].[EmployeeID], [AssociateTbl].[AstFirstName], [AssociateTbl].[AstLastName] FROM AssociateTbl ORDER BY [EmployeeID]; 

I've tried all of the code snippets I have found and none of them have worked. I have also tried putting this code in the 'Criteria' portion of the queries:

[Forms]![PlanFrm]![ReportWhatDatesFrm].[Form]![AstNameCbx]

For example, I tried (to no avail):

 DoCmd.OpenReport "AnnualStmtRpt", acViewReport, , "AssociateTbl.EmployeeID =' " & [Form]![ReportWhatDatesFrm].[AstNameCbx] & " ' "

I think it would be easier to filter the main report on the EmployeeID value of the Combobox selection and link the child/master fields on the subreports.

Does anyone have any ideas or thoughts? If you need more information please let me know.

[Rabbit]

You have an extra space after the first single quote in your criteria and an extra space before the closing single quote.

[mcupito]

Thanks, Rabbit. I got the Combobox to filter the report. Now for the dates, I tried to put

Between [Forms]![ReportWhatDatesFrm].[StartDateTxt] And [Forms]![ReportWhatDatesFrm].[EndDateTxt]

in the 'Criteria' for each query, and it keeps asking for the Parameter Value for those text boxes.

Any ideas on why the queries aren't able to find the dates I am entering in those text boxes?

[Rabbit]

It is forum policy to answer only one question per thread. Please review this forum's posting guidelines and create a new thread for the new question.

[mcupito]

I take it back. The filter for EmployeeID isn't working. Instead of the VBA route, I chose to use the record source for the Combobox and open the Main report using the result from the selection.

However, it seems as though it isn't doing anything. The report opens with the same EmployeeID regardless of what I choose in the Combobox (AstNameCbx).. Any ideas?

[Rabbit]

You will need to explain what you mean by "I chose to use the record source for the Combobox and open the Main report using the result from the selection."

Also, what is the SQL of the source for the combobox?

[mcupito]

SQL for the combobox:

SELECT [AssociateTbl].[EmployeeID], [AssociateTbl].[AstFirstName], [AssociateTbl].[AstLastName] FROM AssociateTbl ORDER BY [EmployeeID];

"I chose to use the record source for the Combobox and Open the Main report using the result from the selection"

-> When a user picks an Employee from the Combobox, I intended to use the EmployeeID of that person to filter the report. Through filtering the report on the EmployeeID, it then filters the subreports by Master/Child fields.

My issue is that the Combobox from the form is not passing the selection (EmployeeID, EmployeeName) to the report. The report keeps loading with the same Employee, regardless of what is selected in the Combobox.

I tried the VBA code rotue, and it keeps asking me to enter the EmployeeID as a Parameter Value:

DoCmd.OpenReport "AnnualStmtRpt", acViewReport, , "EmployeeID = " & Me.AstNameCbx

[Rabbit]

When it asks for a parameter entry, that usually means somethign is misspelled or doesn't exist.

What is the record source SQL for the report?

Check the following things:

1) That the combo box AstNameCbx is bound to the ID field and not another field.

2) That EmployeeID is exists and is spelled the same way in the record source for the report.

3) That the data types between the combo box value and the record source is the same.

4) That the filter string is outputting correctly.

[mcupito]

#2 is the issue. I do not have EmployeeID on the Main report. How would you suggest doing this?

As is, the main report opens with no record source or anything (it did return the same EmployeeID every run through, but I deleted it). I was merely trying to use the Combobox value to filter the Subreports and view the report that way, however it's obvious now that won't work because of #2 being nonexistent.

Should the main report just have a recordsource of the Associate table that lists the Associate's names, that way I can still link to the child/master fields, or should it be something else?

I am trying to list a report, with the 3 subreports, all filtered by EmployeeID. If I use a Combobox to perform this then great, if not, I would create all of the reports for each employee.

Any ideas? Thanks.

[zmbd]

mcupito:

1) Recordsource:
Can be used for a CONTROL SOURCE in a form or control
Can be used for a ROW SOURCE in a combo/list box
Can be used for data manipulation in VBA or Query

These are not merely semantics, but precise vocabulary so that everyone can understand what is being done with what and where it is happening.

2) -filtering-
The following are several examples that cover a majority of situations and can be easily modified to suit your needs:

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

3) Rabbit has given you an answer to the original post.

4) Rabbit has asked you to start a new thread for a new question

[Z{Based on Rabbits subsequent post strike the following}]
[S]5) You have then attempted to ask yet a third question[/S]

Please start a new thread. You may insert a link back to this thread for context should you feel the need.

[Rabbit]

@zmbd, I don't think this is a third issue. It's that we have discovered the root cause of the first issue that the OP thought was resolved but wasn't.

@mcupito, your main report's record source should be something that includes the unique employee ids from your data. This way you would be able to use the master/child to filter the subreport and you can filter the main report through the existing code.

[mcupito]

Rabbit, I used a query that simply has EmployeeID, FirstName and LastName fields as the record source for the report. I linked the subreports to the report via EmployeeID Child/Master fields. It still is not filtering the subreports, and I can't select other employees reports also (the record selector at the bottom - which would be essential for year -end reporting)

Any ideas?

[Rabbit]

We would need more detail, i.e. the record sources for the main report and sub reports, the code that opens the report, any other relevant code or properties, and an explanation of what you mean when you say: "It still is not filtering the subreports, and I can't select other employees reports also (the record selector at the bottom - which would be essential for year -end reporting)"

[mcupito]

The Main report is to contain 3 subreports, all should be filtered by EmployeeID. I.E If there are 10 employees, there should be 10 main reports with 30 subreports.

As is, I have the Recourd Source for the Main report being:

SELECT AssociateTbl.EmployeeID, AssociateTbl.AstFirstName, AssociateTbl.AstLastName
FROM AssociateTbl
ORDER BY AssociateTbl.EmployeeID;
 

Using the Link Master/Child fields, I plan on filtering the subreports by EmployeeID from the above Recourd Source. (This has not worked for me yet.)

The Sub Report Record Sources are as follows:
Award Subreport

 SELECT AwardTbl.EmployeeID, AwardTbl.PlanID, PlanTbl.PlanDesc, AwardTbl.AwardDate, AwardTbl.AwardUnits, AwardTbl.AwardNAV, NAV_Tbl.NetAssetValue, [AwardTbl]![AwardUnits]*[NAV_Tbl]![NetAssetValue] AS CurrentValue, AssociateTbl.AstFirstName, AssociateTbl.AstLastName
FROM (PlanTbl INNER JOIN (NAV_Tbl INNER JOIN AwardTbl ON NAV_Tbl.NAV_Date = AwardTbl.AwardNAV) ON PlanTbl.PlanID = AwardTbl.PlanID) INNER JOIN AssociateTbl ON AwardTbl.EmployeeID = AssociateTbl.EmployeeID
GROUP BY AwardTbl.EmployeeID, AwardTbl.PlanID, PlanTbl.PlanDesc, AwardTbl.AwardDate, AwardTbl.AwardUnits, AwardTbl.AwardNAV, NAV_Tbl.NetAssetValue, [AwardTbl]![AwardUnits]*[NAV_Tbl]![NetAssetValue], AssociateTbl.AstFirstName, AssociateTbl.AstLastName
HAVING (((AwardTbl.AwardDate) Between [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt] And [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![EndDateTxt]));

Forfeiture Subreport

SELECT AssociateTbl.EmployeeID, AwardTbl.ForfeitDate, AwardTbl.ForfeitedUnits, PlanTbl.PlanDesc, PlanTbl.PlanID, NAV_Tbl.NetAssetValue, [AwardTbl]![ForfeitedUnits]*[NAV_Tbl]![NetAssetValue] AS [Current Value], AssociateTbl.AstFirstName, AssociateTbl.AstLastName
FROM NAV_Tbl INNER JOIN ((AssociateTbl INNER JOIN AwardTbl ON AssociateTbl.EmployeeID = AwardTbl.EmployeeID) INNER JOIN PlanTbl ON AwardTbl.PlanID = PlanTbl.PlanID) ON NAV_Tbl.NAV_Date = AwardTbl.ForfeitNAV
GROUP BY AssociateTbl.EmployeeID, AwardTbl.ForfeitDate, AwardTbl.ForfeitedUnits, PlanTbl.PlanDesc, PlanTbl.PlanID, NAV_Tbl.NetAssetValue, [AwardTbl]![ForfeitedUnits]*[NAV_Tbl]![NetAssetValue], AssociateTbl.AstFirstName, AssociateTbl.AstLastName
HAVING (((AwardTbl.ForfeitDate) Between [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt] And [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![EndDateTxt]));
 

Payout Subreport

SELECT AssociateTbl.EmployeeID, PlanTbl.PlanDesc, PayoutTbl.PytDate, PayoutTbl.PytUnits, PayoutTbl.PytNAV, PlanTbl.PlanID, [PayoutTbl]![PytUnits]*[NAV_Tbl]![NetAssetValue] AS [Value], AssociateTbl.AstFirstName, AssociateTbl.AstLastName, NAV_Tbl.NetAssetValue
FROM NAV_Tbl INNER JOIN ((AssociateTbl INNER JOIN (PayoutTbl INNER JOIN AwardTbl ON PayoutTbl.AwardID = AwardTbl.AwardID) ON AssociateTbl.EmployeeID = AwardTbl.EmployeeID) INNER JOIN PlanTbl ON AwardTbl.PlanID = PlanTbl.PlanID) ON NAV_Tbl.NAV_Date = PayoutTbl.PytNAV
GROUP BY AssociateTbl.EmployeeID, PlanTbl.PlanDesc, PayoutTbl.PytDate, PayoutTbl.PytUnits, PayoutTbl.PytNAV, PlanTbl.PlanID, [PayoutTbl]![PytUnits]*[NAV_Tbl]![NetAssetValue], AssociateTbl.AstFirstName, AssociateTbl.AstLastName, NAV_Tbl.NetAssetValue
HAVING (((PayoutTbl.PytDate) Between [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt] And [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![EndDateTxt]));
 

When I open the Main report, the subreports show up, however there is no data. I would like the data that is retrieved from each of the 3 subreports to be filtered for only the EmployeeID on each Main report.

That being said, I am expecting to have multiple reports (1 for each employee) which I have not been able to retrieve also. Hence my reference to not being able to view multiple reports.

It's almost as if the Main report / Subreports aren't communicating.

The code that opens the report is:

        Case 14
            DoCmd.OpenReport "AnnualStmtRpt", acViewReport

Note: I am no longer trying to use a Combobox to filter which employee I want to see the reports for. I want to see the reports for every employee, however, 1 employee per report.

I hope this helps!
Thanks.

[Rabbit]

I recreated the basic structure of your tables and reports and have no problem getting data to show up. This most likely means that your queries don't return any data or that one of your settings is off. I would check each of the following:

1) That the parent and child fields are linked correctly.

2) That the source of each query is returning the correct data.

3) That each subreport opens by itself with the correct data populated.

[mcupito]

It seems to be returning SOME data, however I still can't get the report for every employee. Only the first employee if sorting by EmployeeID ASC is returned, so I can't test if it is truly working or not.

Any ideas on what that could be?

[Rabbit]

Only the three points above come to mind. Can you zip and attach the database to the thread in a 2007 or earlier format?

[mcupito]

Unfortunately, no, because of persons addresses, names and account information. (It would take extremely long to configure dummy data for this particular scenario.)

Is there any information or screen shots I could post that would help you?

[mcupito]

Here's an image of the Main report returning data. I just need a report for every Employee, and that's what I am having trouble doing. I hope that makes sense.

[Rabbit]

Can you just blank out all the sensitive data?

[mcupito]

It would be pretty difficult, and it utilizes a backend. Sorry, I wish I could because it's bothering me.

[mcupito]

Okay, it should be pretty harmless. I included a copy and pasted in the necessary items.

The PW is OH!@

[Rabbit]

A few things wrong with the report.

1) You have detail data in the report header. The report header is only displayed once per report, hence it will only ever show one record. Detail data needs to go in the detail section of the report.

2) You have subreports that are linked to detail data in the page header. Page headers are only displayed once per page. If you have no detail data, then it will never display more than one page, and therefore you will only get one occurrence of your page header. Also, if you have multiple detail records on one page, you will only see the page header data for the first record on that page. The subreports also have to go in the detail section of the report.

3) Your main query is filtered to only one ID. You need to remove that filter.

[mcupito]

#3 was for testing purposes, sorry about that.

Thank you, I did not know that about the Detail section.

Is there a way to create a new main report for each EmployeeID?
As is, it just lists all of the "Detail" sections of the report over and over again, without creating their own report.

[Rabbit]

If you mean you want to open multiple instances of the report, you can't do that unless you want to make a bunch of copies of the reports in Access and I highly suggest you do not do that.

If you mean you want each detail record on its own page, then put in a page break.

[mcupito]

Thanks, Rabbit. You've been a great help.

[Rabbit]

No problem, good luck with the rest of your project.