Solved Update Subform Record Count on a Tab

[Mike Krailo]

Not sure what happened with my previous attempt to post this but I'll put it in again. I have attached a sample database that illustrates the problem I am trying to solve. This is a main form with a tab control on it. On a tab is a single subform and the goal is to update the Caption of the tab so it shows the record count in real time when records are added or deleted.

Adding new records seems to work, but when they are deleted, the record count does not decrement. Its like an order of events issue, but I'm not seeing it.

UPDATE: I figured it out. I was on the right track but got distracted. I ended up changing the delete button from a macro to vba and just calling my sub DoCount from there after the delete and it works now. It still won't work if a user clicks the record selector and then deletes it though.

I'm calling this in the forms current event:

Public Sub DoCount()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim StrSQL As String
 
   On Error GoTo ErrorHandler
 
   StrSQL = "SELECT Account.PID, Account.Account, Account.Password, Account.CustID " _
          & "FROM Account " _
          & "WHERE Account.CustID=" & Nz(Forms!Customer!CustID, 0)

   Set db = CurrentDb
   Set rs = db.OpenRecordset(StrSQL)
   rs.MoveLast
   TempVars!RecCount = rs.RecordCount
   rs.Close
   Set rs = Nothing
   Forms!Customer!TabControl.Pages("Pass").Caption = "Passwords(" & TempVars!RecCount & ")"
   Exit Sub
 
ErrorHandler:
   Select Case Err.Number
      Case 3021 'No Current Record
         TempVars!RecCount = 0
         Forms!Customer!TabControl.Pages("Pass").Caption = "Passwords(" & TempVars!RecCount & ")"
         Exit Sub
      Case Else
         MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Oops, we found an ERROR"
         Resume Next
   End Select
End Sub

 

[pbaldy]

Thanks for updating with your solution. Untested, but try changing the Allow Deletions property of the form to No to force the user to use your button.

 

[Mike Krailo]

I can try that as test, I just ended up turning off record selectors in the subform. I sure would like to know why the TempVars!RecCount variable doesn't update to the correct value in the Current event. I'm missing something there. If anyone knows what is going on there and can explain it.

 

[pbaldy]

I'll try to play with it later, but I notice the record navigation doesn't update after I delete via record selector either:

 

[Mike Krailo]

Good eye. I didn't see that.

 

[jdraw]

Untested, but you may have to use the AfterDelConfirm event to get the record count??

Tested and No!!

 

[Mike Krailo]

I have confirmations turned off so that event doesn't even fire when there is a delete.

 

[bastanu]

Please have a look at the updated file and see if it works for you.
Cheers,
Vlad

 

[Mike Krailo]

Thanks for working with this Vlad. I get Run-time error 2455 "You entered an expression that has an invalid reference to the property Form/Report.

I looked at your code, and that was a different approach to solving this. I had this all working earlier but then tried to expand it to working on two tabs and that's when I realized my approach wasn't going to work.

 

[bastanu]

Where\when do you get the error Mike?

 

[Mike Krailo]

Right at open of the Cutomer form. It hangs up on the lcount assignment line as shown in the above image. Something doesn't quite look right with the data source for the new AssemblySubform that you created.

 

[Gasman]

Phew, that is a relief.
I was getting the same error on the first lCount statement. That too was for sSubForm = "AssemblySubform"

 

[Mike Krailo]

OK, got it working. I changed the record source on the AssemblySubform from Account to Assemblies and changed the PK from PID to AID. Then I had to decompile and then she was back in business. Nice work vlad.

 

[bastanu]

I don't get any errors...what about this file?

 

[Pat Hartman]

Mike,
I'm not sure why you would open a recordset and bring down all the records to determine a count. I would either use dCount() which would at least run on the server and return only one record or the method suggested by Vlad.

To update the counts in the labels, you would have to run the count code in an appropriate procedure such as the form's AfterUpdate which should pickup deletions as well as additions.

 

[Mike Krailo]

@Pat, as you probably already know, there are many ways to get the record count and I don't want to get too deep into that here. I wasn't going for best way to get record count here. The focus here was simply to figure out how to update the tab captions with a count of the records on the subforms in realtime. I do appreciate your advice as always.

@Vlad, looks like you picked the same name I did for the new version of the database And that one opened just fine, not sure why that first one was having problems.

 

[bastanu]

 

[arnelgp]

too much Code.
you may try this minimal coding.

 

[Mike Krailo]

Arnel, that one doesn't update the counts as soon as a record is deleted.

 

[arnelgp]

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