Solved

Count of records in Subform

Posted on 2015-01-31
3
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 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