open a message box if a query is null during an autoexec macro

[vander]

Hope someone can help with this.

I have a query that runs using an AutoExec macro when the database opens.
What I want is for a message box to open instead of the query results window,
telling me if the query produced any records. I can do this easily enough
through a command button on a form, but cant figure out how to do it through
an autoexec macro at startup.

Any suggestions?

 

[Dorian]

You need to have your Autoexec macro run a VB function (macro action
RunCode)that executes the query and captures the count of records retrieved.

Dim strSQL as string
Dim k as integer
Dim rs as new ADODB.recordset
strSQL = "SELECT count(*) As K FROM MyTable WHERE........."
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If rs.EOF Then
k = 0
Else
k = rs.Fields("K").Value
End If
Msgbox "Found " & K & " records",,"Record Count"

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".