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

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.

Answer 1

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)

Answer 2

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!

Answer 3

Thank you very much Peter. This worked perfectly. I appretiate the time you took to answer my question.

Answer 4

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.