Updating a subform field (1 Viewer)

[scottrie2]

I have an unbound text box called ChildCount on a subform. It's code is =count(*). When I add a new record in the subform, it does not update. I have tried to add Me!ChildCount.requery to the code that adds a new record, but that doesn't seem to work. If I move to a new family (main form) and then back, the number is correct. Any ideas on updating the count? This is Access 2016.

 

[theDBguy]

Hi,


Try using Recalc in place or Requery.


Hope it helps...

 

[scottrie2]

No recalc available. I tried anyway and the code failed.
I did find I could do Me.Recalc, but that did not work either.

 

[theDBguy]

Which event did you use for Me.Recalc? Is "Me" referring to the subform? If not, you might try changing it to point to the subform. For example: Me.SubformName.Form.Recalc

 

[scottrie2]

I have the code as part of the add new record routine. If a user clicks the button to add a record this code is run:

If MsgBox("Add new child?", vbYesNo + vbQuestion) = vbYes Then
DoCmd.GoToRecord , , acNewRec
Me.Form.Recalc
End If

The current code is not updating the ChildCount text box.

 

[theDBguy]

Hi,


DoCmd.GoToRecord , , acNewRec does not add a new record, so the count won't be any different yet. You'll have to save the new record first before recounting the number of records.

 

[scottrie2]

That was what I thought as well, but then where do I put the code to do the recount?

 

[theDBguy]

Well, after you navigate the subform to a new record using the GoToRecord command, how exactly does a new record get added? Does the user enter the data? If so, you can do the recount in the AfterUpdate event of the form (subform).

 

[scottrie2]

That did the trick. I did have to add this code as well to force the form to update (save) as well.

If me.dirty then
me.dirty = false
end if

 

[theDBguy]

Congratulations! Glad to hear you got it sorted out. Good luck with your project.