CODEWITHSUNDEEP
vbams-accessvbscript
Ian Fafard
Ian Fafard

Reputation: 51

MS Access VBA: VBA command based on Query Result

Is there any way I can use the query result as a condition in a VBA module containing a dynamic sql statement??

For example

if( count(user_id) from table > 0, THEN xport and send email to a,b,c)

I am trying to make the condition rely on a specific query result.. so if a certain query has ANY results at all, I will have the module DO something to react to that result.

The only other way I tried was to query the database from EXCEL, and have excel's VBA react to a certain cell's value (which would contain the same condition I am trying to get in access.. but id rather STAY in access as MUCH as possible)

Thanks!

Upvotes: 1

Views: 661

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

DCount is clearly the way to go in Access. If you were not in Access, you would need to create a recordset and test its value

Sub TestRecordCount()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open msCONNSTRING

    Set rs = cn.Execute("SELECT COUNT(*) FROM table")

    If rs.Fields(0).Value > 0 Then
        'do stuff
    End If

    rs.Close
    cn.Close

End Sub

Since the recordset only returns one record with one field, you can test .Fields(0).Value to see how many records are in the table.

Upvotes: 1

Gustav
Gustav

Reputation: 55806

Use DCount:

If DCount("*", "[table]") > 0 Then
    ' Insert code to export and send email to a,b,c.
Else
    ' Don't.
End If

Upvotes: 1

Related Questions