Hi Gang,

I have a main form (one), which has a subform (many) in continuous form
view. I'd like to show on the main form, how many records there are in the
current subform. This figure will change as I move from record to record on
the main form.

I don't seem to be able to use the recordcount property with a form object
so I am having trouble getting this to work.  Can I somehow copy the
subform's current recordset to a snapshot in VBA and then use the
recordcount property??

Any other ideas appreciated?

TIA,

Andrew

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1E5ED.E3C2AA40
Content-Type: text/plain

Hi Andrew,

This is how I would do it:-

Create a text box control in the sub-form's footer and set the visible
property to NO. Set the control source property to =Count(<<any field which
will always have a value i.e. Parent ID>>). Let's say we name this control
txtTotalRecs.
Now in the parent form create another control and set the control source
property to =[<<sub-form name>>].Form![txtTotalRecs]

Hope this helps.

Omar

___________________________
Omar Chaudry
Bowman Power Systems
Belvidere Road
Southampton
SO14 5QY
Tel: 023 80 352924

-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...] 
Sent: 17 April 2002 09:31
To: Access
Subject: [access] Recordcount of Sub-Form

Hi Gang,

I have a main form (one), which has a subform (many) in continuous form
view. I'd like to show on the main form, how many records there are in the
current subform. This figure will change as I move from record to record on
the main form.

I don't seem to be able to use the recordcount property with a form object
so I am having trouble getting this to work.  Can I somehow copy the
subform's current recordset to a snapshot in VBA and then use the
recordcount property??

Any other ideas appreciated?

TIA,

Andrew





  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.

In the form module

Dim lngRecordCount as Long
Dim rsCopy as (DAO.Recordset or ADODB.Recordset)
set rsCopy = me.RecordsetClone
rsCopy.MoveLast
lngRecordCount=rsCopy.RecordCount

The cloned recordset is an exact copy of the form's recordset.  Therefore,
you can set either recordsets bookmark = to the bookmark to reposition the
current record pointer.

|-----Original Message-----
|From: Haslett, Andrew [mailto:andrew.haslett@i...]
|Sent: Wednesday, April 17, 2002 1:31 AM
|To: Access
|Subject: [access] Recordcount of Sub-Form
|
|
|Hi Gang,
|
|I have a main form (one), which has a subform (many) in continuous form
|view. I'd like to show on the main form, how many records there are in the
|current subform. This figure will change as I move from record to record on
|the main form.
|
|I don't seem to be able to use the recordcount property with a form object
|so I am having trouble getting this to work.  Can I somehow copy the
|subform's current recordset to a snapshot in VBA and then use the
|recordcount property??
|
|Any other ideas appreciated?
|
|TIA,
|
|Andrew
|
|
|

use =count(*) as the control source of a textbox on the subform. If it has
to appear on the mainform use a second textbox.with
=subform.form!firsttextbox.
David
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Wednesday, April 17, 2002 6:01 PM
To: Access
Subject: [access] Recordcount of Sub-Form


Hi Gang,

I have a main form (one), which has a subform (many) in continuous form
view. I'd like to show on the main form, how many records there are in the
current subform. This figure will change as I move from record to record on
the main form.

I don't seem to be able to use the recordcount property with a form object
so I am having trouble getting this to work.  Can I somehow copy the
subform's current recordset to a snapshot in VBA and then use the
recordcount property??

Any other ideas appreciated?

TIA,

Andrew

Andrew,

<< I have a main form (one), which has a subform (many) in continuous form
view. I'd like to show on the main form, how many records there are in the
current subform. This figure will change as I move from record to record on
the main form. >>

Create an unbound textbox in the main form that's going to keep count of 
the no. of subform records (called, for example, txtSubRecCount).  On the 
main form's "On Current" event put this one line:

   Me.txtSubRecCount = Dcount("[any field in the subform's underlying _
   table that is always populated]", "{name of subform}", "[{field in _
   subform you want to match}] = '" & Me.[{field in main form you want _
   it to match to}] & "'")

Greg

<< Me.txtSubRecCount = Dcount("[any field in the subform's underlying _
   table that is always populated]", "{name of subform}", "[{field in _
   subform you want to match}] = '" & Me.[{field in main form you want _
   it to match to}] & "'") >>

Correction:

   Me.txtSubRecCount = Dcount("[any field in the subform's underlying _
   table that is always populated]", "{name of subform's underlying _
   table}", "[{field in subform you want to match}] = '" & Me.[{field _
   in main form you want it to match to}] & "'")

Greg

Thanks all! Much appreciated.

A

-----Original Message-----
From: Gregory Serrano [mailto:SerranoG@m...]
Sent: Thursday, 18 April 2002 7:38 AM
To: Access
Subject: [access] Re: Recordcount of Sub-Form


<< Me.txtSubRecCount = Dcount("[any field in the subform's underlying _
   table that is always populated]", "{name of subform}", "[{field in _
   subform you want to match}] = '" & Me.[{field in main form you want _
   it to match to}] & "'") >>

Correction:

   Me.txtSubRecCount = Dcount("[any field in the subform's underlying _
   table that is always populated]", "{name of subform's underlying _
   table}", "[{field in subform you want to match}] = '" & Me.[{field _
   in main form you want it to match to}] & "'")

Greg