Update parent form

I have a continuous subform with a record counter on the main form.

When I delete a record from the subform the record counter on the main form does not update immediately.

I have requery and refresh  just about every where but it will not update until I close and reopen.

How can I be programming with access for 20 years and not answer this stupid question. Why is something so basic so difficult in access (2002)
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How is the record counter populated?

In many cases, I find it best to re-run the routine that populates those sort of values directly, instead of refreshing/requerying the form.

For example, if I'm running a DCount to populate a textbox, I'd just run that DCount directly again when I need to:

Me.Parent.txRecCount = DCount("SomeField", "SomeTable", "SomeField=SomeValue")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
No points please, I'm like Scott, I like to explicitly call the code that does that type of thing.  If there are several steps that need to be performed, put it in a subroutine in the forms code module and simply call it after the delete.

Depending on how you are deleting the record, you might use the "AfterDelConfirm" event for the form to call this code.
Derek BrownMDAuthor Commented:
Hi both

From the subforms on delete proceedure I have tried

Private Sub Form_Delete(Cancel As Integer)
Forms![SetsMain]![Text7] = DCount("[DoorNumber]", "SelectSet")
End Sub

I have also tried the on delete confirm but as I disabled warnings that does not work either. I just don't get it.

If I open the selected query it shows the correct number of records but not when I call it from the form.

Whilst writing this it has occurred to me that perhaps it is not working because I am using the same query that is the underlying query for the subform. Maybe that is the problem?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Whilst writing this it has occurred to me that perhaps it is not working because I am using the same query that is the underlying query for the subform. Maybe that is the problem?
Could be, but that's hard to say. In my opinion, you're much better off going directly back to the source, so if "SelectSet" is a query, then go back to the underlying tables and get the data from those instead of the query.
Dale FyeCommented:
Sometimes, I'll put code like that in the timer event and set the timer interval to something like 100.  Then, in the Timer event, I'll set the interval back to zero and run the code.  

So, put this in the Delete event of the subform:

Private Sub Form_Delete(Cancel as Integer)

    debug.print "Delete"
    me.Parent.TimerInterval = 100

End Sub

Then, in the Timer Event of the main form:

Private Sub Form_Timer()

    me.TimerInterval = 0
    me.[Text7] = DCount("[DoorNumber]", "SelectSet")

End Sub
Derek BrownMDAuthor Commented:
Thank you both.

It was indeed the underlying query that was the problem. Copied it called it version 2 and referred to that instead of the subforms original and Bingo!!

What a crazy world Access sometimes is.
Derek BrownMDAuthor Commented:
Thanks both
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.