Multiple fields in ControlSource

Question

I have a report where the user specifies the sequence as either Employee Name or Social Sec Number.   Accordingly, I set the Me.GroupLevel(0).ControlSource = "EmpName" or Me.GroupLevel(0).ControlSource = "SocSecNbr"  All works fine EXCEPT when I have 2 employees with the same name but different SSNs.  When I group by "EmpName", all the detail records for both emplyees are in the same group.  How do I keep the detail records for the same SSN together but sort in EmpName sequence??

Thanks for your time and considerations.

Mike Bromley

Answer 1

Ken, thanks again for your help.  The problem is fixed.  Initially, I only had the report print in SSN sequence.  Therefore, I only had one group on the Sorting and Grouping Dialog Box.  When I was asked to print the report either in SSN sequence or EmpName sequence, I added the EmpName to the Sorting and Grouping Dialog Box but it was AFTER the initial SSN group.  So, even though I specified GoupLevel (0).ControlSource = "EmpName" and GroupLevel(1).ControlSource = "SSN"  within the same name there was never a control break for a different SSN.  When I changed the order of the fields in the Sorting and Grouping Dialog Box so that the highest sort order was listed first all worked perfectly.

Mike Bromley

Answer 2

The code looks as though it should do the trick.  The important thing is that all summary and aggregation is done at the level of the innermost group, so in the sorting and grouping dialogue, the top group should have no header or footer, the next group down, which should permanently be by SSN, should have a header and footer.  Whether the outer (top) group is set to EmpName or SSN the fact that the inner group is always by SSN should keep the data per employee separate when they have the same name as there will two sets of headers, detail and footers for the same name, one for each SSN.

Answer 3

Thank you , Ken for your help.  I already have the headings in the SSN header and the totals in the SSN footer.  I do not have any headers or footers for EmpName, but I do have EmpName in the Sorting and Grouping Dialog Box for the report with  just "NO" for headers and footers.  In the code, I have the following:

Select Case Forms!frmReportParms!frameSequence

          Case 1

                 Me.GroupLevel(0).ControlSource = "SSN"

          Case 2

                 Me.GroupLevel(0).ControlSource = "EmpName"

                 Me.GroupLevel(1).ControlSource = "SSN"

 End Select

As before, all is fine except I have 2 employees named Robert Smith but they have different SSNs.  My report lists all the detail records for both Robert Smiths together instead of taking a control break when the SSN changes.  The totals are for both Robert Smith's detail records combined.  I'm obviously missing something.

Thanks again,

Answer 4

You need two group levels, the first by EmpName, the second by SSN.  If you are showing controls in a header and/or footer put these in the SSN header.   When you change the sort order of the report in code it's the first group level you change, so for sorting by SSN both group levels would be by SSN, when sorting by name the first would be by EmpName, the second by SSN.