Possible to turn off grouping programatically

[Drew]

I am using Access for reporting from SQL Server and would like to know if it
is possible to programmatically turn off grouping in Access. The report is
generated from a stored procedure and can generate 2 different recordsets.
The first report (by 0,1,2) is a good deal of records, but the second report
(by 3) has only like 10 records. I would like to be able to turn off the
grouping if the user wants the second report. I have the following stored
procedure,

CREATE PROCEDURE spCheckReceiving
@Shift int
AS
IF @Shift = 1
SELECT TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName,
EP.EmpSSN, P.PosTimeKeeper, D.DeptName
FROM dbo.tblEmpInfo EF INNER JOIN
EmpCore.dbo.tblEmployee E ON EF.EmpID = E.EmpID INNER
JOIN
EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
EP.EmpID INNER JOIN
EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
JOIN
EmpCore.dbo.tblTimeKeepers TK ON P.PosTimeKeeper =
TK.TimeKeeperID INNER JOIN
EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID
WHERE (E.EmpActive = 1) AND (EF.EmpDirectDeposit = 0) AND (E.EmpShift =
1 OR E.EmpShift = 2 OR E.EmpShift = 0)
ORDER BY E.EmpLName, E.EmpFName
ELSE
SELECT TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName,
EP.EmpSSN, P.PosTimeKeeper, D.DeptName
FROM dbo.tblEmpInfo EF INNER JOIN
EmpCore.dbo.tblEmployee E ON EF.EmpID = E.EmpID INNER
JOIN
EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
EP.EmpID INNER JOIN
EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
JOIN
EmpCore.dbo.tblTimeKeepers TK ON P.PosTimeKeeper =
TK.TimeKeeperID INNER JOIN
EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID
WHERE (E.EmpActive = 1) AND (EF.EmpDirectDeposit = 0) AND (E.EmpShift =
3)
ORDER BY E.EmpLName, E.EmpFName
GO

Is this possible?

Thanks,
Drew Laing

 

[Martin J]

Yes, Look for 'grouplevel property' in vba help. You will need a command in
report open that looks like this without the if logic to change the control
source to a field that is already sorted.

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

employee is already sorted if you have an extra header or footer section for
the grouping you will need to cancel printing them in there code
respectively with an if then cancel=true.

HTH
Martin J

 

[Marshall Barton]

I am using Access for reporting from SQL Server and would like to know if it
is possible to programmatically turn off grouping in Access. The report is
generated from a stored procedure and can generate 2 different recordsets.
The first report (by 0,1,2) is a good deal of records, but the second report
(by 3) has only like 10 records. I would like to be able to turn off the
grouping if the user wants the second report. I have the following stored
procedure,

Group levels in a report can be "disabled" by using code in
the report's Open event. Just set the GroupLevel's
ControlSource property to a constant expression such as =1:

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

The group header and footer will still print so you will
probably want to make those sections invisible:

Me.Section(5).Visible = False
Me.Section(6).Visible = False

But maybe you still want the grouping field to be the sort
order. If so, then I guess all you really wanted was to
make the group header/footer invisible.

 

[Drew]

Ok... I see this, but how do I find out what the user entered in the Stored
Procedure prompt?

Thanks,
Drew Laing

 

[Marshall Barton]

Sorry, Drew, I missed that part of your question. Since I
know nothing about SQL Server, I shouldn't have tried to
answer.

Hopefully, someone else will jump in and provide an answer
for you. If not, post another question specifically about
the stored procedure issue.