?
Solved

Count of records in Subform

Posted on 2015-01-31
3
Medium Priority
?
454 Views
Last Modified: 2015-02-01
Update Count of records in Subform when a record is deleted.

I have a main form with a continuous subform. I want a number of records in the subform value in a text box on the main form.  I have tried  an unbound control that gets it's value from a DLookUp but I cannot get it to update when a record is deleted from the subform. There must be a simple solution to this.
0
Comment
Question by:DatabaseDek
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40582094
try using the afterDelConfirm event of the subform
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 40582130
One way is to put a hidden unbound textbox in the subform's header or footer with the following expression:
=Count([aFieldInYourRecordsource])
Then in the unbound textbox in the main form, use something like this:
=[nameOfSubformControl].Form.[nameOfHiddenTextbox]

The disadvantage with the above approach is there seems to be a delay in getting textbox to recalculate.  If you need a more immediate response, you can use the AfterDelConfirm event as mentioned by Rey with the following code:
Parent.nameOfTextboxOnMainForm = Me.RecordsetClone.RecordCount

If you're inserting records and you want the count updated, you need to add the same code to the AfterInsert event.  Also, if you want to see the count when you first open the form, you should do the same on the subform's OnOpen event.

You can do something like this:
Private Sub Form_AfterDelConfirm(Status As Integer)
    Call RefreshCount
End Sub

Private Sub Form_AfterInsert()
    Call RefreshCount
End Sub

Private Sub Form_Open(Cancel As Integer)
    Call RefreshCount
End Sub

Private Sub RefreshCount()
    Parent.txtCount = Me.RecordsetClone.RecordCount
End Sub

Open in new window

Ron
0
 

Author Closing Comment

by:DatabaseDek
ID: 40582289
You wouldn't think that something that simple could require such work.

Brilliant!

Thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question