Prev: Date/Time criteria problem
Next: Concatenate multiple rows
From: vander on 28 Apr 2010 12:33
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?

From: Dorian on 28 Apr 2010 13:56
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".


"vander" wrote:

> 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?
>
> .
>
 | 
Pages: 1
Prev: Date/Time criteria problem
Next: Concatenate multiple rows