Dcount in a report

[Brian]

I already figured out another way but the question I have is in the
report open how come the following code comes up with a parameter box
asking for input

Me("txtNumOfTrees").ControlSource = DCount("UNITID", "tblPlots",
"Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand = '"
& [Forms]![frmSummarize]![cboStand] & "' ")

 

[Marshall Barton]

I already figured out another way but the question I have is in the
report open how come the following code comes up with a parameter box
asking for input

Me("txtNumOfTrees").ControlSource = DCount("UNITID", "tblPlots",
"Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand = '"
& [Forms]![frmSummarize]![cboStand] & "' ")

Because the result of the DCount is not a record source
field name.

I would probably set a label control's Caption property
instead of a text box's ControlSource property.

If you really need to use a text box's ControlSource, then
you can set it to an expression by concatenating the DCount
value to an = sign.

Me("txtNumOfTrees").ControlSource = "=" & DCount(...

 

[Brian]

One more question .. I have the following code

Set Rst = db.OpenRecordset("Select DISTINCT Species from tblPlots where
Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand = '"
& [Forms]![frmSummarize]![cboStand] & "'", dbOpenSnapshot)
Rst.MoveLast
Me("txtNumPlots").ControlSource = Rst.Recordcount
rs.Close: Set rs = Nothing
Rst.Close: Set Rst = Nothing

that also comes up with a parameter box asking for input and once you
put something in there it shows up on the report but otherwise does
not.. and this is one that i have done before with the exception being
that instead of the select statement I just used the Me.RecordSource of
the report itself.. TIA

I already figured out another way but the question I have is in the
report open how come the following code comes up with a parameter box
asking for input

Me("txtNumOfTrees").ControlSource = DCount("UNITID", "tblPlots",
"Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand = '"
& [Forms]![frmSummarize]![cboStand] & "' ")

Because the result of the DCount is not a record source
field name.

I would probably set a label control's Caption property
instead of a text box's ControlSource property.

If you really need to use a text box's ControlSource, then
you can set it to an expression by concatenating the DCount
value to an = sign.

Me("txtNumOfTrees").ControlSource = "=" & DCount(...

 

[Brian]

Ok I see what you mean I put in
Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount

now what if I wanted some text in behind of that number such as
Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount & "test"

One more question .. I have the following code

Set Rst = db.OpenRecordset("Select DISTINCT Species from tblPlots where
Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand = '"
& [Forms]![frmSummarize]![cboStand] & "'", dbOpenSnapshot)
Rst.MoveLast
Me("txtNumPlots").ControlSource = Rst.Recordcount
rs.Close: Set rs = Nothing
Rst.Close: Set Rst = Nothing

that also comes up with a parameter box asking for input and once you
put something in there it shows up on the report but otherwise does
not.. and this is one that i have done before with the exception being
that instead of the select statement I just used the Me.RecordSource of
the report itself.. TIA

I already figured out another way but the question I have is in the
report open how come the following code comes up with a parameter box
asking for input

Me("txtNumOfTrees").ControlSource = DCount("UNITID", "tblPlots",
"Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand =
'" & [Forms]![frmSummarize]![cboStand] & "' ")

Because the result of the DCount is not a record source
field name.

I would probably set a label control's Caption property
instead of a text box's ControlSource property.

If you really need to use a text box's ControlSource, then
you can set it to an expression by concatenating the DCount
value to an = sign.

Me("txtNumOfTrees").ControlSource = "=" & DCount(...

 

[Marshall Barton]

If all you are trying to do is display the number of records
in the report, then you don't need any of that code. You
can just use a text box in the report (or a group) header or
footer section with an expression like:
=Count(*) & " some text"

If you are counting something else, then, because ot the
DISTINCT predicate, what you have should take care of it.
However, opening a recordset will NOT prompt you for
anything. The report is probably where the prompt is
generated. Whatever you are prompted for is the field name
that you have mistyped.
--
Marsh
MVP [MS Access]

Ok I see what you mean I put in
Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount

now what if I wanted some text in behind of that number such as
Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount & "test"

One more question .. I have the following code

Set Rst = db.OpenRecordset("Select DISTINCT Species from tblPlots where
Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand = '"
& [Forms]![frmSummarize]![cboStand] & "'", dbOpenSnapshot)
Rst.MoveLast
Me("txtNumPlots").ControlSource = Rst.Recordcount
rs.Close: Set rs = Nothing
Rst.Close: Set Rst = Nothing

that also comes up with a parameter box asking for input and once you
put something in there it shows up on the report but otherwise does
not.. and this is one that i have done before with the exception being
that instead of the select statement I just used the Me.RecordSource of
the report itself.. TIA

Brian wrote:

I already figured out another way but the question I have is in the
report open how come the following code comes up with a parameter box
asking for input

Me("txtNumOfTrees").ControlSource = DCount("UNITID", "tblPlots",
"Compartment = '" & [Forms]![frmSummarize]![cboComp] & "' and Stand =
'" & [Forms]![frmSummarize]![cboStand] & "' ")


Because the result of the DCount is not a record source
field name.

I would probably set a label control's Caption property
instead of a text box's ControlSource property.

If you really need to use a text box's ControlSource, then
you can set it to an expression by concatenating the DCount
value to an = sign.

Me("txtNumOfTrees").ControlSource = "=" & DCount(...

 

[Brian]

Problem is the Rst recordset is not the recordsource so I had to use a
new one.. reason being the report and the underlying table are dynamic
and have to be so the count just comes from another table and I was
hoping that there was a way to add some text...
so
Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount

becomes something like

Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount & "test"

which does not work by the way but I was just trying some things out

 

[Marshall Barton]

OK, I see what the issue is now. The concatenation is
occuring in your code and the resulting control source
expression is:
=123test
which is an undefined named.

You can try using either:
Me("txtNumPlots").ControlSource = "=" & Rst.Recordcount
& " & ""test"""
which will result in the control source expression:
=123 & "test"

or
Me("txtNumPlots").ControlSource = "=""" & Rst.Recordcount
& "test"""
which will result in:
="123test"

A simple alternative to all that would be to move the code
out of the report's Open event and into the Format event of
the section (Report Header?) that contains txtNumPlots.
Then you can set the text box's value directly:
Me.txtNumPlots = Rst.Recordcount & "test"