Urgent! Object reference problem?

[mdmarney]

I know I have posted this before, but am having trouble getting a good answer.
Here is the current code:
It works great in a form, but not in a report.

Dim rs As Object
Set rs = CurrentDb.OpenRecordset("Select * from tbl_Attendants Where Tee_Shirt_Size = 'L'&quot
rs.MoveLast
Me.tx_L_Count = rs.RecordCount

**The error is on the assignment to the text box line.**
It says, "You can't assign a value to this object."

Me!tx... Does nott help anything.
Thanks!
*************
M. MARNEY

http://www.tgw.com

 

[randysmid]

Hi,
Are you simply trying to count the number of "L" tshirts? In this case, you don't need to do it this way. There is a much simpler way using the DCount function.

Here are the steps to follow:
1) Add a text box to the report (we can call it txtShirtCount). It doesn't even have to be visible.
2) The control source for this text box would be:
=DCount("[Tee_Shirt_Size]","tbl_Attendants", "[Tee_Shirt_Size] = 'L'&quot HTH,
Randy Smith
California Teachers Association

 

[VicM]

mdmarney:

Try: Me!tx_L_Count.Value = rs.RecordCount

Hope this helps,

Vic

 

[jebry]

Hi!

One more question (we are a curious bunch!), where is the code you posted?

Jeff Bridgham
bridgham@purdue.edu

 

[mdmarney]

Randy,
Thank you for the code.
I need to add another field to the where clause though.
Basically...
"[Tee_Shirt_Size] = 'L' AND [Group_ID] = '&[Current_Group_ID]'"
Will this work? *************
M. MARNEY

http://www.tgw.com

 

[mdmarney]

Tried what i had above, but got error. I think I am probably referencing the Current_Group_ID wrong. Any thoughts? *************
M. MARNEY

http://www.tgw.com

 

[randysmid]

Almost got it!!!!

This is what you are showing me:
"[Tee_Shirt_Size] = 'L' AND [Group_ID] = '&[Current_Group_ID]'"

And this is what the final is supposed to be (presuming that Group_ID is string datatype):
=DCount("[Tee_Shirt_Size]", "tbl_Attendants", "[Tee_Shirt_Size] = 'L'" AND [Group_ID] = '" & [Current_Group_ID] & "'&quot

STRINGS MUST HAVE SINGLE QUOTE MARKS SURROUNDING THEM. IF NUMERIC, then no quote marks. Here is a sample if the Group_ID is numeric:
=DCount("[Tee_Shirt_Size]", "tbl_Attendants", "[Tee_Shirt_Size] = 'L'" AND [Group_ID] = [Current_Group_ID]&quot HTH,
Randy Smith
California Teachers Association

 

[mdmarney]

Randy,
It is of LONG type. How should it be different?
Thanks! *************
M. MARNEY

http://www.tgw.com

 

[mdmarney]

Randy,
Ok, re-read your post and say the answer to my last. Sorry. The code you provided was invalid. I assumed the double-quote after L' was misplaced.
Still having trouble though...

=DCount("[Tee_Shirt_Size]","tbl_Attendants","[Tee_Shirt_Size] = 'L' AND [Parish_Group_ID] = [Parish_Group_ID]&quot
This code returns the value as if Parish_Group_ID was disregarded.
??? *************
M. MARNEY

http://www.tgw.com

 

[randysmid]

HI,
This should work:

=DCount("[Tee_Shirt_Size]", "tbl_Attendants", "[Tee_Shirt_Size] = 'L'" AND [Group_ID] = " & [Current_Group_ID]) HTH,
Randy Smith
California Teachers Association

 

[mdmarney]

Did not work. Syntax error - Missing Operand!
One thing that might be causing problems: My actual code reads:
=DCount("[Tee_Shirt_Size]", "tbl_Attendants", "[Tee_Shirt_Size] = 'L'" AND [Parish_Group_ID] = " & [Parish_Group_ID])

How does it know that I want the Parish_Group_ID that corresponds to the current page of the report? *************
M. MARNEY

http://www.tgw.com

 

[Rick39]

Try

=DCount("[Tee_Shirt_Size]", "tbl_Attendants", "[Tee_Shirt_Size] = 'L' AND [Parish_Group_ID] = " & [Parish_Group_ID])

 

[DonQuichote]

By the way, mdmarney,

Your original code will probably work if you put it in the right print event (for the report detail section: Detail_Print, etc.)
Just try it.

Best regards