Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Counting records on subform (access 97 sr2)

    I have a main form and a sub form.
    Both linked with master child field.
    The main form is the unique client, the sub form is the associated comments.
    Users can add comments on sub form but no change to the master form.

    I want to have a label to say "Client has 5 comments" or other such lazy comments (users need it spelling out)
    The following I have in the main form form_current (no error trapping as yet)
    <pre>Private Sub Form_Current()
    Dim lngCommCnt As Long
    lngCommCnt = Me.tbl_Trans_Comments.Form.RecordsetClone.RecordCo unt
    If lngCommCnt > 1 Then
    Me.lblComments.Caption = "Client has " & lngCommCnt & " comments"
    Else
    Me.lblComments.Caption = "Comments"
    End If
    End Sub
    </pre>



    The count does not appear to be catching the correct number at the time of the current rcord.
    I stepped the code and saw that the lngcount was saying 1, yet hovering over the recordsetclone.recordcount showed me 5

    Have I got the code in the right section - form_current?
    Is my sub form simply not updating fast enough to be caught in the form_current?
    Should I place the code in the sub form events? tried this in current/activate/go focus et cetera but no progress.

    TIA
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting records on subform (access 97 sr2)

    It works OK for me in Access 2003, but it's probably a timing problem. Try the following (make a copy of the form or of the entire database first):

    - Open the subform in design view.
    - Place a text box anywhere on the subform.
    - Set its Visible property to No.
    - Set its Control Source to =Count(*)
    - Set its Name to txtCount.
    - Close and save the subform.

    - Open the main form in design view.
    - Remove or comment out the Form_Current code.
    - Delete the lblComments label.
    - Put a text box in its place.
    - Set the Control Source of the text box to

    =IIf([tbl_Trans_Comments]![txtCount]>1,"Client has " & [tbl_Trans_Comments]![txtCount] & " comments","Comments")

    - Close and save the main form.

    When you reopen the main form and move from record to record, you should see the correct count appear with a small delay (how much depends on the database and on your system).

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Counting records on subform (access 97 sr2)

    Excellent HansV.
    Keeps it simple and it works fine.

    With respect to the original setup I stepped through the code but paused on the first line.
    When paused before stepping on in the code then the label worked fine, but when I stepped straight through it then then code was simply too fast to catch the sub forms refresh time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •