none
vba code to count all records in a table which meet field1=0 and field2=0 RRS feed

  • Question

  • I have a table (table1) that has two numeric fields (field1 and field2) and other text fields. When I click button1 in my form1 I want to run a query like this:

    SELECT COUNT([field1]) as countpending FROM [table1] where [field1] = 0 And [field2] = 0

    if the result of the previous query (countpending) is 0 (zero) then I want button2 to be enabled (it will be greyed out if the condition is not met).

    I would really appreciate if you could provide me the code how to do this, I use Access 2016. I have searched all over the internet but I cannot find an answer.

    Thanks in advance.

    Saturday, July 14, 2018 1:58 AM

Answers

  • Hi William,
    It is important that you use reusable code in your projects. With the following function you can count records effectively in any situation.

    Remember always that there are two important different procedures: a Sub procedure and a Function procedure. A Function procedure can return a value. For example, FindRecordCount returns the value of the number of records. All you have to do is pass the correct SQL to the strSQL argument. What you should NOT do is use the SQL you give in your example. See my example in button1_Click.

    Place this function in a standard module so that you can use the function for multiple purposes.
    Function FindRecordCount(strSQL As String) As Long
        Dim db      As DAO.Database
        Dim rst     As DAO.Recordset
     
        On Error GoTo Bye_Err
     
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL)
        
        If rst.EOF Or rst.BOF Then
           FindRecordCount = 0
        Else
           rst.MoveLast
           FindRecordCount = rst.RecordCount
        End If
     
    Bye_End:
        Set rst = Nothing
        Set db = Nothing
        Exit Function
    Bye_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume Bye_End
    End Function

    An example of how you can use the function.

    Private Sub button1_Click()
        Dim lngCount    As Long
        
        lngCount = FindRecordCount("SELECT * FROM Table1 WHERE Field1 = 0 AND Field2 = 0")
        If lngCount = 0 Then
            button2.Enabled = True
        Else
            button2.Enabled = False
        End If
    End Sub

    Hopefully this is your solution. Success!





    Saturday, July 14, 2018 7:04 AM

All replies

  • Private Sub Button1_Click()
        Me.Button2.Enabled = (DCount("[field1]", "[table1]", "[field1]=0 AND [field2]=0") = 0)
    End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 14, 2018 6:54 AM
  • Hi William,
    It is important that you use reusable code in your projects. With the following function you can count records effectively in any situation.

    Remember always that there are two important different procedures: a Sub procedure and a Function procedure. A Function procedure can return a value. For example, FindRecordCount returns the value of the number of records. All you have to do is pass the correct SQL to the strSQL argument. What you should NOT do is use the SQL you give in your example. See my example in button1_Click.

    Place this function in a standard module so that you can use the function for multiple purposes.
    Function FindRecordCount(strSQL As String) As Long
        Dim db      As DAO.Database
        Dim rst     As DAO.Recordset
     
        On Error GoTo Bye_Err
     
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL)
        
        If rst.EOF Or rst.BOF Then
           FindRecordCount = 0
        Else
           rst.MoveLast
           FindRecordCount = rst.RecordCount
        End If
     
    Bye_End:
        Set rst = Nothing
        Set db = Nothing
        Exit Function
    Bye_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume Bye_End
    End Function

    An example of how you can use the function.

    Private Sub button1_Click()
        Dim lngCount    As Long
        
        lngCount = FindRecordCount("SELECT * FROM Table1 WHERE Field1 = 0 AND Field2 = 0")
        If lngCount = 0 Then
            button2.Enabled = True
        Else
            button2.Enabled = False
        End If
    End Sub

    Hopefully this is your solution. Success!





    Saturday, July 14, 2018 7:04 AM
  • Thank you very much Peter. This worked perfectly. I appretiate the time you took to answer my question.
    Saturday, July 14, 2018 4:53 PM
  • Thank you Hans. I had explored before the option to use DCount and it didn't seem to work for me. Thanks for taking the time to answer.
    Saturday, July 14, 2018 4:54 PM