Solved

Count of records in Subform

Posted on 2015-01-31
3
399 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 119

Expert Comment

by:Rey Obrero
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now