none
Counter on a Button? RRS feed

  • Question

  • Hello. I have a button on my Main Menu, pointing to a datasheet form. Is there a possibility to display a counter on the button, showing the number of records currently in the form? ... and also this number to update automatically with every change of the number of records? Thank you in advance for any thoughts!
    Tuesday, March 5, 2019 8:28 AM

Answers

  • The form which has the "Cases" calculation must be open in order for the record count to show. If the button is on the Main Menu but the form the record count is on a different form that is NOT open, then you must use the DCount function to show the count on the Main Form. So if you want to show the count on a form that is NOT open then:

    Me.btnRecordCounter.Caption = "Cases " & DCount("*","Query or Table Name where the Cases are located"). Let's say the table or query name where Cases are located is named MyCases. Then:

    Me.btnRecordCounter.Caption = "Cases " & DCount("*","MyCases").

    • Marked as answer by Access_fan Thursday, March 7, 2019 12:49 PM
    Wednesday, March 6, 2019 5:51 PM
  • If you are counting records on another form than a parent (main) form, then you must pass the full name of this, like:

    Private Sub Form_Current()   

    Forms!NameOfYourMainForm!btnRecordCounter.Caption = _     "Cases " & Me.RecordsetClone.RecordCount

    End Sub



    Gustav Brock

    • Marked as answer by Access_fan Thursday, March 7, 2019 12:49 PM
    Wednesday, March 6, 2019 6:42 PM

All replies

  • Insert code like this in the OnCurrent, AfterDelete, and AfterInsert events of the subform:

    Me.Parent!YourButton.Caption = Me.RecordsetClone.RecordCount


    Gustav Brock

    Tuesday, March 5, 2019 12:30 PM
  • You should be able to use DCount and use it to populate the caption.  You could use the forms close event to then update the count/label.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, March 5, 2019 12:41 PM
  • This is very encouraging - thank you very much! I am still not getting the result I want, possibly missing something from your instructions. I do not see After Delete in the Property Sheet, but rather AfterDelConfirm (there is also just Delete); Did you have in mind any of these two?

    Here is the code (Command64 is the name of the button), but clicking the button sends me to the Debugger:

    Private Sub Form_AfterDelConfirm(Status As Integer)
    Me.Parent!Command64.Caption = Me.RecordsetClone.RecordCount
    End Sub

    Private Sub Form_AfterInsert()
    Me.Parent!Command64.Caption = Me.RecordsetClone.RecordCount
    End Sub

    Private Sub Form_Current()
    Me.Parent!Command64.Caption = Me.RecordsetClone.RecordCount

    End Sub

    Tuesday, March 5, 2019 2:31 PM
  • Yes, it is AfterDelConfirm, sorry.

    Gustav Brock

    Tuesday, March 5, 2019 2:37 PM
  • Thank you for this! But where and how do I use the DCount function? I checked Access help, but there is no example how to use in a code, and I am new to coding.

    In the form Close event, what would be the code to update count/label?

    Tuesday, March 5, 2019 2:57 PM
  • You are not going to be able to use a Command Button for this. You will need to use a Text Box. In the the text box Control Source property use the following:

    ="Your Text Here " & DCount("*","Your Query Name Here")

    Tuesday, March 5, 2019 3:41 PM
  • But then what do  I need to change in the code so that I get the result (number of records in the form) displayed on the Command Button? One more thing ... I do not have a subform; it is just this command button that when clicked opens a form in datasheet format. But all I want to achieve is see the number of records in this datasheet form displayed on the button at all times (even without clicking it).
    Tuesday, March 5, 2019 8:54 PM
  • If the code is causing an error, then which code line is causing the error? Set breakpoints on all the code lines and then when the error occurs push F8 to step through the code lines. At least you will know which line is causing the error.

    You can also try:
    Me.Parent!Command64.Caption = "Record Count: " & DCount("*","Your Datasheet Record Source Name")

    That will display the record count when the form is opened on the button (place code in the forms On Open Event). Getting the button to update is another matter.

    Also, are you sure Me.Parent!Command64.Caption is correct? Should it be Me.Command64.Caption instead? You should not be using Parent! unless you are referring to a Parent form. You don't have a Parent form if you arn't using subforms.

    Tuesday, March 5, 2019 9:51 PM
  • > But all I want to achieve is see the number of records in this datasheet form displayed on the button at all times (even without clicking it).

    That's what my code does. Tested and works. So what's the problem?


    Gustav Brock

    Wednesday, March 6, 2019 6:54 AM
  • I am now able to make it work when I place the button on the form with the records itself; with your code, the button shows the number of current records on the form. But if the button is on another form (the Main Menu in my case), I am not able to get it to work. Here is the code I use that works when the button is on the form with the records:

    Private Sub Form_Current()
        Me.btnRecordCounter.Caption = _
        "Cases " & Me.Recordset.RecordCount
    End Sub

    When button is on the main menu, should I replace Me. with the form name that the number of records are coming from? I tried this, but its not working ... I feel I am very close, but do you see what am I doing wrong?

    Private Sub Form_Current()
        Me.btnRecordCounter.Caption = _
        "Cases " & [Form_Name].Recordset.RecordCount
    End Sub

    Wednesday, March 6, 2019 4:54 PM
  • The form which has the "Cases" calculation must be open in order for the record count to show. If the button is on the Main Menu but the form the record count is on a different form that is NOT open, then you must use the DCount function to show the count on the Main Form. So if you want to show the count on a form that is NOT open then:

    Me.btnRecordCounter.Caption = "Cases " & DCount("*","Query or Table Name where the Cases are located"). Let's say the table or query name where Cases are located is named MyCases. Then:

    Me.btnRecordCounter.Caption = "Cases " & DCount("*","MyCases").

    • Marked as answer by Access_fan Thursday, March 7, 2019 12:49 PM
    Wednesday, March 6, 2019 5:51 PM
  • If you are counting records on another form than a parent (main) form, then you must pass the full name of this, like:

    Private Sub Form_Current()   

    Forms!NameOfYourMainForm!btnRecordCounter.Caption = _     "Cases " & Me.RecordsetClone.RecordCount

    End Sub



    Gustav Brock

    • Marked as answer by Access_fan Thursday, March 7, 2019 12:49 PM
    Wednesday, March 6, 2019 6:42 PM
  • This is it!!! Thank you so much!
    Thursday, March 7, 2019 12:50 PM
  • Thank you so much - now it works!!!
    Thursday, March 7, 2019 12:51 PM