Count number of rcd on continuous form.

[Dennis]

Hi,

I'm running Access via Office XP Pro.

I'm trying to create a small call tracking system. I have a "Call" form
what the basic call information is maintained in one tab. Pleas note that
one "call" can be comprised of multiple phone calls dues to resolving issues,
phone tag, etc.

I have a second tab, which is a sub-form. It has a continuous form / view.
This form has the date and time of each individual phone call as well as the
notes for that call. The sub-form's record source is a query that the
sub-form then filters by the sub-form Parent Child Relationship in the
form/sub-form properties (I'm not sure I said that right).


I have a three part question.
How do I count the number of records (individual phone calls) on the
continuouse form - sub-form. I've seen Allen Browns' seen code:

With Me.RecorsetClone
If .RecordCount > 0 Then
..MoveLast
End If
End With

My question is, to what do I set me.resordsetclone equal to?




2. In what event on the sub-form do I place the code?
- I want the count to be displayed in the footing.
- And as I add records to the continuous form, I want the count to update
automatically.



3. And based upon the count, I want a field set to a given value. For
example, if the count is 1, the field is set to one value. If the count is
2, the field is set to another value. The field I'm setting is a status
field. It will contain the number of calls, but once closed it will also
contain a status of closed (for different reasons), and it will contain a
status of private. These last status codes will be set manually.

I know I can generate the count using group by. however that approach
leaves me with a read only query, which causes problem if I try to use it as
an input to a data entry form.

Thanks,


Dennis

 

[Arvin Meyer [MVP]]

Questions 1 & 2:

Add an unbound textbox to your continuous form's footer. Set it's
controlsource to:

= Me.RecordsetClone.RecordCount

If it's possible to have an empty subform, use this in the controlsource:

=IIf([NewRecord],"New Record",Count(*))

Question 3:

Use the form's Current event with a Select Case statement to read the value
and make the decision (aircode):

Sub Form Current()
Select Case Me.RecordsetClone.RecordCount
Case 1
' Set a value
Case 2
' Set another value
Case Else
' Do something else
End Select
End Sub

 

[Dennis]

Arvin,



Your comment: If it's possible to have an empty subform, use this in the
controlsource:

=IIf([NewRecord],"New Record",Count(*))

My response: Very nice. It is works great when the screen is displayed.

However, this count is NOT updated when I add new records to the continuous
form. How do I fix that?





Your comment:
Use the form's Current event with a Select Case statement to read the value
and make the decision (aircode):


Sub Form Current()
Select Case Me.RecordsetClone.RecordCount
Case 1
' Set a value
Case 2
' Set another value
Case Else
' Do something else
End Select
End Sub


The on current event does not fire for adding a new record. If fires after
the record has been added and it moves on the next record.

when I add a record, I need to look at one of the fields on the new current
record. I guess I could put you code in the after update of the field. Or
should I put it in both places (via call) so it gets processed both ways?


Dennis

 

[Arvin Meyer [MVP]]

Did you put it in a text box in the subform footer?

If not, do so. As soon as you move out of the record, it will update.