Record sorting and report total conflicting!

[Guest]

I have a report whose Record Source is defined in VBA code (because the query is dependent upon interactive user record selection criteria). There is therefore no Grouping and Sorting in the report itself. The report generates fine and sorts according the ORDER BY clause of my query

However, if I create a total control to total one of the detail fields (control source is "=Sum([HistAmt])") in the report footer, the total calculates correctly but the sorting of the records no longer works! If I place the total in the page footer, then the sorting is correct, but I get "#Error" instead of a calculation (not that I want the total in the page footer anyway)

Any ideas on what's going on here

ctda

 

[Duane Hookom]

You need to set the sorting and grouping levels in the report. You can do
this dynamically with VBA Code.

--
Duane Hookom
MS Access MVP

I have a report whose Record Source is defined in VBA code (because the

query is dependent upon interactive user record selection criteria). There
is therefore no Grouping and Sorting in the report itself. The report
generates fine and sorts according the ORDER BY clause of my query.

However, if I create a total control to total one of the detail fields

(control source is "=Sum([HistAmt])") in the report footer, the total
calculates correctly but the sorting of the records no longer works! If I
place the total in the page footer, then the sorting is correct, but I get
"#Error" instead of a calculation (not that I want the total in the page
footer anyway).

 

[Duane Hookom]

Create all you possibly group levels while in design mode and set them to a
constant expression like
=1
Then save your report. You should then be able to modify the control source
property when opening the report.

--
Duane Hookom
MS Access MVP

OK good idea, but how? I have tried to follow the F1 help info for

creating sorting/grouping using VBA code, but can't get it to work. Here's
what I have tried in the On Open event of the report:

Me.GroupLevel(0).ControlSource = gstrSingleAcctGroup0Field

The global string variable is a RecordSource field, but I get a run-time

error indicating there is no sorting/grouping field defined for the group
level number used. I thought this statement was doing exactly that -
defining the field.

I then tried the following instead, but you have to be in report design

view to do this, which I don't want. I'm trying to generate a report using
VBA code, not design one.

intGroupLevel0 = CreateGroupLevel("rpt_SingleAcctHistory",

gstrSingleAcctGroup0Field, 0, 0)

Help my understanding please.

ctdak


----- Duane Hookom wrote: -----

You need to set the sorting and grouping levels in the report. You can do
this dynamically with VBA Code.

--
Duane Hookom
MS Access MVP

I have a report whose Record Source is defined in VBA code (because

the
query is dependent upon interactive user record selection criteria). There
is therefore no Grouping and Sorting in the report itself. The report
generates fine and sorts according the ORDER BY clause of my query. fields
(control source is "=Sum([HistAmt])") in the report footer, the total
calculates correctly but the sorting of the records no longer works! If I
place the total in the page footer, then the sorting is correct, but I get
"#Error" instead of a calculation (not that I want the total in the page
footer anyway).

 

[Guest]

OK, I got it. Thanks for your help. What you suggested here works great. In fact, even if you leave the group expression blank for each level in the report itself it seems to work just fine to modify the control source from VBA code
ctda

----- Duane Hookom wrote: ----

Create all you possibly group levels while in design mode and set them to
constant expression lik
=
Then save your report. You should then be able to modify the control sourc
property when opening the report

--
Duane Hooko
MS Access MV

OK good idea, but how? I have tried to follow the F1 help info fo

creating sorting/grouping using VBA code, but can't get it to work. Here'
what I have tried in the On Open event of the reporterror indicating there is no sorting/grouping field defined for the grou
level number used. I thought this statement was doing exactly that
defining the fieldview to do this, which I don't want. I'm trying to generate a report usin
VBA code, not design one

intGroupLevel0 = CreateGroupLevel("rpt_SingleAcctHistory" gstrSingleAcctGroup0Field, 0, 0
Help my understanding please
ctda
You need to set the sorting and grouping levels in the report. Yo

can d
this dynamically with VBA Code

--

Duane Hooko
MS Access MV

I have a report whose Record Source is defined in VBA code (becaus

th
query is dependent upon interactive user record selection criteria) Ther
is therefore no Grouping and Sorting in the report itself. Th repor
generates fine and sorts according the ORDER BY clause of my query field
(control source is "=Sum([HistAmt])") in the report footer, the tota
calculates correctly but the sorting of the records no longer works If
place the total in the page footer, then the sorting is correct, bu I ge
"#Error" instead of a calculation (not that I want the total in th pag
footer anyway)