MS Access VBA: VBA command based on Query Result

Question

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!

Of course there is; Access is a desktop database, and it would be pretty useless if you couldn't retrieve values from that data in your code. This is pretty basic stuff, and it seems like you should find an Access tutorial to learn those basics. — Ken White
– Ken White, Commented Aug 26, 2015 at 2:35
Look at the Application.DCount Method topic in Access' help system. — HansUp
– HansUp, Commented Aug 26, 2015 at 3:13
Thanks, im transitioning 90% of my reporting and knowledge functions to Access so the VBA transition is a bit hectic for the timeline I need to complete - waiting for my book to come in on it and I appreciate the on the spot help folks. — Ian Fafard
– Ian Fafard, Commented Aug 27, 2015 at 4:10

Gustav · Accepted Answer · 2015-08-26 06:33:21Z

1

Use DCount:

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

answered Aug 26, 2015 at 6:33

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Fafard Over a year ago

That's fantastic, I was getting the same results but with a lot more vba. I had been using excel and automating a macro that queries a particular table, using functional cells as variables outside of VBA, that way editing variables and such was much faster. However I don't see anything faster and as simple as the code above.

Dick Kusleika · Accepted Answer · 2015-08-26 13:39:49Z

1

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.

answered Aug 26, 2015 at 13:39

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Collectives™ on Stack Overflow

MS Access VBA: VBA command based on Query Result

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related