Filtering a query for the DCount function

[John S. Ford, MD]

I'm creating a report that does NOT have an underlying query bound to it.
Instead, it has a large number of TextBoxes that are populated using
different DCount statements that use queries.

Can I put some code in the OnOpen event of the report that creates a
recordset filtered by parameters entered into a form that opens the report
and then use that recordset as the source query for all the DCount
statements that fill the TextBoxes of my report?

I hope this makes sense. The help file for DCount just says that I can use
a table or query for the <domain> parameter (nothing about a recordset).

John

--
-------------------------------------------------------
John S. Ford, MD, MPH
Assistant Professor of Medicine
David Geffen School of Medicine at UCLA
Harbor-UCLA Medical Center
(e-mail address removed)
http://califmedicineman.blogspot.com

 

[Duane Hookom]

You might want to add a Record Source to your report and move all you
DCount() controls to the Report Header section. Set the detail section to 0
height so it doesn't display. Then you can use [RecordSource] in the place
of "tblDomainTable".

For instance in Northwind, you can set the Record Source of a report to
[Products]. Add a text box to the Report Header section with a control
source of:
=DCount("*",[RecordSource],"ProductID<=34")
This will display a count of the number of products with a ProductID <=34.

 

[Phil Smith]

A recordset is simply the records returned from a query...right?

Just build a query filtered by parameters on the form. Have each Dcount
reference that query. No need to do anything to the report, just put a
button on the form to open the report, after making sure all of the
parameters are correctly filled in. Or am I missing something?

 

[John S. Ford, MD]

Dear Duane,

If I do that, when I print the report won't it generate a new (repeating)
page for each individual record?

John

You might want to add a Record Source to your report and move all you
DCount() controls to the Report Header section. Set the detail section to 0
height so it doesn't display. Then you can use [RecordSource] in the place
of "tblDomainTable".

For instance in Northwind, you can set the Record Source of a report to
[Products]. Add a text box to the Report Header section with a control
source of:
=DCount("*",[RecordSource],"ProductID<=34")
This will display a count of the number of products with a ProductID <=34.


--
Duane Hookom
MS Access MVP

I'm creating a report that does NOT have an underlying query bound to it.
Instead, it has a large number of TextBoxes that are populated using
different DCount statements that use queries.

Can I put some code in the OnOpen event of the report that creates a
recordset filtered by parameters entered into a form that opens the report
and then use that recordset as the source query for all the DCount
statements that fill the TextBoxes of my report?

I hope this makes sense. The help file for DCount just says that I can
use
a table or query for the <domain> parameter (nothing about a recordset).

John

--
-------------------------------------------------------
John S. Ford, MD, MPH
Assistant Professor of Medicine
David Geffen School of Medicine at UCLA
Harbor-UCLA Medical Center
(e-mail address removed)
http://califmedicineman.blogspot.com

 

[Duane Hookom]

If you don't have any height to the detail section then there shouldn't be
any more than a single page. Try it...

--
Duane Hookom
MS Access MVP

Dear Duane,

If I do that, when I print the report won't it generate a new (repeating)
page for each individual record?

John

You might want to add a Record Source to your report and move all you
DCount() controls to the Report Header section. Set the detail section to 0
height so it doesn't display. Then you can use [RecordSource] in the
place
of "tblDomainTable".

For instance in Northwind, you can set the Record Source of a report to
[Products]. Add a text box to the Report Header section with a control
source of:
=DCount("*",[RecordSource],"ProductID<=34")
This will display a count of the number of products with a ProductID
<=34.


--
Duane Hookom
MS Access MVP

I'm creating a report that does NOT have an underlying query bound to it.
Instead, it has a large number of TextBoxes that are populated using
different DCount statements that use queries.

Can I put some code in the OnOpen event of the report that creates a
recordset filtered by parameters entered into a form that opens the report
and then use that recordset as the source query for all the DCount
statements that fill the TextBoxes of my report?

I hope this makes sense. The help file for DCount just says that I can
use
a table or query for the <domain> parameter (nothing about a
recordset).

John

--
-------------------------------------------------------
John S. Ford, MD, MPH
Assistant Professor of Medicine
David Geffen School of Medicine at UCLA
Harbor-UCLA Medical Center
(e-mail address removed)
http://califmedicineman.blogspot.com

 

[John S. Ford, MD]

Thanks Duane,

I'll try it!

John
\

If you don't have any height to the detail section then there shouldn't be
any more than a single page. Try it...

--
Duane Hookom
MS Access MVP

Dear Duane,

If I do that, when I print the report won't it generate a new (repeating)
page for each individual record?

John

You might want to add a Record Source to your report and move all you
DCount() controls to the Report Header section. Set the detail section

to
0

height so it doesn't display. Then you can use [RecordSource] in the
place
of "tblDomainTable".

For instance in Northwind, you can set the Record Source of a report to
[Products]. Add a text box to the Report Header section with a control
source of:
=DCount("*",[RecordSource],"ProductID<=34")
This will display a count of the number of products with a ProductID
<=34.


--
Duane Hookom
MS Access MVP



I'm creating a report that does NOT have an underlying query bound to it.
Instead, it has a large number of TextBoxes that are populated using
different DCount statements that use queries.

Can I put some code in the OnOpen event of the report that creates a
recordset filtered by parameters entered into a form that opens the report
and then use that recordset as the source query for all the DCount
statements that fill the TextBoxes of my report?

I hope this makes sense. The help file for DCount just says that I can
use
a table or query for the <domain> parameter (nothing about a
recordset).

John

--
-------------------------------------------------------
John S. Ford, MD, MPH
Assistant Professor of Medicine
David Geffen School of Medicine at UCLA
Harbor-UCLA Medical Center
(e-mail address removed)
http://califmedicineman.blogspot.com