How do I reference an object in a form?

[mdmarney]

I'm trying to make the following assignment.
tx_L_Count = rs.RecordCount

I have tried.
Me.tx_L_Count = rs.RecordCount
and
Reports!rpt_Shirt_Distribution_by_Diocese_and_Group.tx_L_Count = rs.RecordCount
and neither works.

Help?
Thank you! *************
M. MARNEY

http://www.tgw.com

 

[SBendBuckeye]

Try using the DCount function instead like this:

In the declarations section

Private mdbs As Database
Private mqdf As QueryDef

Private Sub Form_Load()
Dim strRecordSource As String
If InStr(1, Me.RecordSource, "Select", vbDatabaseCompare) > 0 Then
Set mdbs = CurrentDb
Set mqdf = mdbs.CreateQueryDef("TempQuery_" & Me.Name, Me.RecordSource)
strRecordSource = mqdf.Name
Else
strRecordSource = Me.RecordSource 'Will be either a table or query name
End If
tx_L_Count = DCount("LName", [strRecordSource])
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Not (mdbs Is Nothing) Then
mdbs.QueryDefs.Delete mqdf.Name
mdbs.QueryDefs.Refresh
mdbs.Close
Set mdbs = Nothing
Set mqdf = Nothing
End If
End Sub

The code will create and delete a temporary querydef if you are using an SQL string. If the count could change put the text box = line in OnCurrent code istead.

Good Luck!

 

[kewo99]

If you are trying to display the record count for the current form, this works:

Dim rs as recordset
set rs = me.RecordsetClone
tx_L_Count = rs.RecordCount

Ken

 

[DseeDm]

Maybe, I'm missing something....

Is rs a query?

If so you'll need to move to the last record before rs.RecordCount will have a value other than 0.

 

[SBendBuckeye]

Recordset clone is a copy of whatever recordset is driving the current form. I believe you should do an rs.movelast before checking the record count.

 

[mdmarney]

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."
I would like to find a simpler answer than the code above (though I am greatful) as I have little time and experience.
Thanks! *************
M. MARNEY

http://www.tgw.com

 

[SBendBuckeye]

I'm a little confused by your last question. Is the error on the form or are you trying to put in on a report control somewhere? Is the control in question a bound control? Do use a calculated value like this the control should not be pointing to a field value from the table or query which is the record source for the form.

Hope this helps and good lucK!

 

[mdmarney]

Hope I can clarify.
This code in on a Report (On Open).
tx_L_Count is what is being acted upon (as the code indicates) and is Unbound.
That's about all I know to say.
The Recordset for the actual report is different than the one being defined in this code, but I thought since it worked in a form it should work in a report...
Help? *************
M. MARNEY

http://www.tgw.com

 

[SBendBuckeye]

What are you trying to do with the value when you show it on the report? Thanks.

 

[mdmarney]

Nothing, just display it! *************
M. MARNEY

http://www.tgw.com