Solved Update Subform Record Count on a Tab

[gemma-the-husky]

Depending on how you delete the record, there may be no after delete event available. Delete's are a pain at times.

You could have a procedure in the form to reset the tab caption, but you might not be able to automate it, because there is no afterdelete event.
Without testing I am not sure whether a delete then causes the current event to fire, so you could try the current event.

The count is shown in the subform. You could show the count just underneath the caption in a coloured text box.
Wouldn't that work as well?

 

[Minty]

You could use a record count to determine if a record has been deleted.

Ah - wait a minute...

 

[Mike Krailo]

Dave, I'm not sure I follow you on the text box under the caption. Having it in the actual caption makes it possible to see all the counts on all tabs at the same time. Imagine having 5 or 6 tabs all with current record counts. That's what I was going for. It's kind of a challenge to see if it was possible to do. So far Vlads solution works well and I'm happy with it. I forgot to mark this thread as solved.

I could never get that count in the subform to sync up with adds and just left that visible so you could see the difference.

 

[jdraw]

Mike,

What editor are you using to get colored text and vba keywords? Just curious.

Also, it as unclear to me whether you were looking for record count in the parent table or the related subform info.

 

[Mike Krailo]

That is just the colors I selected in the Tools > Options > Editor Format. Nothing special, just have to set it up one time and the colors are set.

The record count was just for one named subform on each tab of a tab control. Those get updated when the main record changes as well.

 

[Ivy]

you need to add code to the subform's AfterDelConfirm event.

Hi arnelgp,

Sorry, I need your help about count records on Tabs. I have tried your code on my database but I have always error and I don't understand where edit it

Thank you very much in advance

 

[Gasman]

You need a ( at the end of every tab caption.

 

[Ivy]

You need a ( at the end of every tab caption.

Thank you very much Gasman!!

Now count records on tabs works, but I have always error on this code:

strCaption = Left$(ctl.Parent.Caption, InStr(ctl.Parent.Caption, "(") - 1)

I have added only a ( at the end of every name of tab caption

 

[Gasman]

You are opening somewhere where ctl is as below?

? ctl.Parent.Caption

? ctl.Name
EtaPensione606567anniM

Note no caption, so same error as you had with no ( in the caption.

The rest is up to you.

 

[Ivy]

You are opening somewhere where ctl is as below?

? ctl.Parent.Caption

? ctl.Name
EtaPensione606567anniM

Note no caption, so same error as you had with no ( in the caption.

The rest is up to you.
View attachment 113822

Yes, it's this:

But it's only a submask, it isn't in tabs, I don't understand because it causes the error, it has nothing to do with tabs. If I delete that submask, error debug in effect disappears. I don't know, it won't like that submask

 

[Gasman]

You should not be passing that?
Public Function fncRecCount(ByRef f As Form)

Otherwise add a test to ensure you have a parent before trying to use that object.

? f.Name
DipendenteM
? ctl.Parent.Caption
? ctl.Name
EtaPensione606567anniM

Try amending to

 If TypeOf ctl Is SubForm And ctl.Parent.Name <> f.Name Then

and then test.

 

[Ivy]

You should not be passing that?
Public Function fncRecCount(ByRef f As Form)

Otherwise add a test to ensure you have a parent before trying to use that object.

? f.Name
DipendenteM
? ctl.Parent.Caption
? ctl.Name
EtaPensione606567anniM

Try amending to

If TypeOf ctl Is SubForm And ctl.Parent.Name <> f.Name Then

and then test.

Thank you very much Gasman,
now it seems to work without problems

I thought I had to put the name TabCaption in f.Name, but instead you don't have to put anything, I didn't quite understand but the important is that it works without giving errors.

The count of new records doesn't update automatically but never mind, it's already a lot to have the record numbers in the tabs.

Thanks so much again Gasman for all your help and patience

 

[Gasman]

The problem appears to be for that subform the main form is the parent and as before, that parent does not have a ( in its caption. As you do not want that in for the mainform, I just tested the parent against the form name passed in. This works for your situation atm.
As for not updating, you will need to call that function on any record additions/deletions.
I will let @arnelgp handle that.

 

[arnelgp]

here is the modified function:

'arnelgp
Public Function fncRecCount(ByRef f As Form)
Dim ctl As Control
Dim strCaption As String
Dim i As Integer
For Each ctl In f.Controls
    If TypeOf ctl Is SubForm Then
        strCaption = ctl.Parent.Caption
        i = InStrRev(strCaption, ")")
        If i <> 0 Then
            strCaption = Left$(strCaption, i - 1)
        End If
        i = InStrRev(strCaption, "(")
        If i <> 0 Then
            strCaption = Left$(strCaption, i - 1)
        End If
        strCaption = strCaption & "(" & ctl.Form.Recordset.RecordCount & ")"
        ctl.Parent.Caption = strCaption
    End If
Next
End Function

also i added some code on the Main form.
and on each subform (on the tab), i added another code to their Current event.

 

[Ivy]

here is the modified function:

'arnelgp
Public Function fncRecCount(ByRef f As Form)
Dim ctl As Control
Dim strCaption As String
Dim i As Integer
For Each ctl In f.Controls
    If TypeOf ctl Is SubForm Then
        strCaption = ctl.Parent.Caption
        i = InStrRev(strCaption, ")")
        If i <> 0 Then
            strCaption = Left$(strCaption, i - 1)
        End If
        i = InStrRev(strCaption, "(")
        If i <> 0 Then
            strCaption = Left$(strCaption, i - 1)
        End If
        strCaption = strCaption & "(" & ctl.Form.Recordset.RecordCount & ")"
        ctl.Parent.Caption = strCaption
    End If
Next
End Function

also i added some code on the Main form.
and on each subform (on the tab), i added another code to their Current event.

Thank you thank you thank you very much Arnelgp!! Now it works perfectly!

 

[Gasman]

That replaces the mainform caption?

 

[Ivy]

That replaces the mainform caption?
View attachment 113844

It works to me, I have name of mainform (DipendenteM), from the last attached file of arnelgp:

 

[Gasman]

It works to me, I have name of mainform (DipendenteM), from the last attached file of arnelgp:

View attachment 113852

Not sure how that worked for you then?

 

[Ivy]

Not sure how that worked for you then?

It's ok the form name in caption's main form, I need the name of main name there:

And other little problem.. when I add new records, the count records works fine and it's ok, but when I click on other tabs I have all font colors black Why does the formatting change?