?
Solved

Count of records in Subform

Posted on 2015-01-31
3
Medium Priority
?
440 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 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

800 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