Count of records doesn't get updated on Add New Record event 1

[inso18]

Hi All.
I have a main form and a sub form which have underlying table and child table.
There's a field in my table which is a count result of the records in it's child table. ([mainTable].[CountField])

My problem is that each time I add a new record to the sub form, count the records and update [CountField] on the main table/form, it gets updated only with the count of the records in the subform _before_ the new record was added.

I tried to refresh the subform on Add New Record event, before I count the records and update [CountField], but it doesn't help.

After the process finished. When I count the records, it does return the correct result.

Do you know the soulution?
Thanks a lot,
inso.

 

[TheAceMan1]

How are ya inso18 . . .

First, its [blue]never recommended[/blue] to store a calculated value! However in your case it is a simple count.

There's more to this than meets the eye because in maintaining proper count you have to account for:
[ol][li]Posting initial count on [blue]opening of the form.[/blue][/li]
[li]Adding [blue]new records only.[/blue] [purple]Not edited previously saved records![/purple][/li]
[li]Record Deletions . . . this [blue]depends on an option.[/blue][/li][/ol]
I'll cover the above in order. [blue]Note:[/blue] don't know the name of your count field so I'm using [purple]qCnt[/purple]. Change proper where you see it.
[ol][li]In the mainforms [blue]On Load[/blue] event, copy/paste the following:

[blue]   Dim sfrm As Form
   
   Set sfrm = [[purple][b]YourSubFormName[/b][/purple]].Form
   
   sfrm.Recordset.MoveLast
   Me![purple][b]qCnt[/b][/purple] = sfrm.Recordset.RecordCount
   sfrm.Recordset.MoveFirst
   
   Set sfrm = Nothing[/blue]

[/li]
[li][blue]Saving new records only[/blue] is a little tougher because the [blue]NewRecord[/blue] property is valid in the forms [blue]BeforeUpdate[/blue] event . . . not the forms [blue]AfterUpdate[/blue] event. So we'll have to set/reset a declared variable proper.

So . . . in the declaration section of the subForms code module, copy/paste the following line:

[blue]Private flgNewRec As Boolean[/blue]

Copy/paste the following line to the subForms [blue]BeforeUpdate[/blue] event:

[blue]   flgNewRec = Me.NewRecord[/blue]

Copy/paste the following to the subForms [blue]AfterUpdate[/blue] event:

[blue]   If flgNewRec Then
      Me.Parent![purple][b]qCnt[/b][/purple] = Me.Recordset.RecordCount
      flgNewRec = False
   End If[/blue]

[/li]
[li]For deleting, if [purple]Tools - Options - Edit/Find Tab - Confirm Section - Document deletions is checked[/purple], copy/paste the following to the subForms [blue]AfterDelConfirm[/blue] event:

[blue]   If Status = acDeleteOK Then
      Me.Parent![purple][b]qCnt[/b][/purple] = Me.Recordset.RecordCount
   End If[/blue]

[/li]
[li][blue]Done![/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[inso18]

Hello TheAceMan1

Sorry for not replying earlier, I've been away.

I appreciate your answer a lot, it is very simple and works fine.

There's a small problem - the counter is getting updated only after I add a new value And then move with the mouse pointer to another record.

Can it be fixed?

Thanks,
inso.

 

[TheAceMan1]

[blue]There's a small problem - [purple]the counter is getting updated only after I add a new value And then move with the mouse pointer to another record.[/purple][/blue]

Thru the [blue]user interface[/blue], the only time recordcount changes is when you save or delete a record. [purple]Saving an edited record occurs whenever you navigate to a different record or the add new record line . . .[/purple]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[inso18]

Saving an edited record occurs whenever you navigate to a different record or the add new record line

Is there a way to do it through a VBA command?

 

[TheAceMan1]

inso18 . . .

[blue]   DoCmd.RunCommand acCmdSaveRecord[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[inso18]

I wrote the command at the end of after_update sub and access still updates the count only after I click on another record.

Maybe I should put the command somewhere else?

Thanks for your feedback
inso.

 

[TheAceMan1]

inso18 . . .

I have to apologize, as I've been dealing with this as if your countfield is on the subform. In your post origination you clearly state:

[blue]There's a field in my table which is a count result of the records in it's child table. ([purple][mainTable].[CountField][/purple])[/blue]

So . . . remove all previous code I've given, then in a module in the modules window, copy/paste the following routine:

[blue]Public Sub CurRecCnt()
   Dim frm As Form, sfrm As Form, rst As DAO.Recordset
   
   Set frm = Forms![[purple][b][i]mainFormName[/i][/b][/purple]]
   Set sfrm = frm![[purple][b][i]subFormName[/i][/b][/purple]].Form
   Set rst = sfrm.RecordsetClone
   
   If rst.BOF Then
      frm [[purple][b][i]CountFieldName[/i][/b][/purple]] = 0
   Else
      rst.MoveLast
      frm![[purple][b][i]CountFieldName[/i][/b][/purple]] = rst.RecordCount
   End If
   
   Set rst = Nothing
   Set sfrm = Nothing
   Set frm = Nothing

End Sub[/blue]

Then in the MainForms [blue]On Current[/blue] event and the subForms [blue]AfterUpdate[/blue] event, copy/paste the following line:

[blue]   Call CurRecCnt[/blue]

Thats it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[inso18]

Now it works well.

DoCmd.RunCommand acCmdSaveRecord

also works!

Thanks a lot!

inso.