CODEWITHSUNDEEP
ms-access-2013
swydell
swydell

Reputation: 2022

Calculated control displays the total number of records that appear in the subform

My assignment is to create a calculated control that displays to total number of Members in the subform. How can I accomplish this when there is no definitive field that I can use in the expression. There are only three fields in the subform: First Name, Last Name, and Phone. If I do something like this =[frmPlanMemberSubform].[Form]![FirstName] that only calculate and displays the first name of the member in the subform. Actually there are only two names in the subform. Theoretically I suppose to get back a count of 2. But I can't figure out how to do it with the existing fields in the subform. Any Access experts out there? Please help. Here is what the database looks like in form view. As you can see there is nothing in the Total Members control box.

enter image description here

Upvotes: 1

Views: 2556

Answers (1)

Alexander Remesch
Alexander Remesch

Reputation: 623

enter image description hereFollow these steps:

1) In the code of the master form insert a function similar to this:

Private Function NumRecords()
    Dim rec As Recordset
On Error GoTo lbErr
    Set rec = Me!<subform-name>.Form.RecordsetClone
    rec.MoveLast
    NumRecords = rec.RecordCount
lbExit:
    Exit Function
lbErr:
    MsgBox Error, vbExclamation
    Resume lbExit
End Function

2) In the field to display the number of records insert the following string in the value property:

=NumRecords()

3) Create the Form_Current trigger as follows:

Private Sub Form_Current()
    Me!<fieldname>.Requery
End Sub

enter image description here enter image description here

enter image description here

Upvotes: 1

Related Questions