very new stumped on VBA code for query

[VeryNew]

Hello,
Can someone offer some advice. I am trying to write a little function that when called will run a query to tell me if there are matching records in the two tables - if there are i need it to give me a message box stating so. I had tried once and could not make it work. I am looking in an Access 97 book and came up with this so far
Private Function TrialQuery()
Set dbCurrent = CurrentDb()
strSQL = "SELECT [Premium Failures].* FROM [Premium Failures] INNER JOIN [Premium Failures (2)] ON ([Premium Failures].[B/L #] = [Premium Failures (2)].[B/L #]) AND ([Premium Failures].[Details 9AM_10:30_Sat] = [Premium Failures (2)].[Details 9AM_10:30_US]) AND ([Premium Failures].Terminal = [Premium Failures (2)].Terminal) AND ([Premium Failures].[Date of Failure] = [Premium Failures (2)].[Date of Failure]);"
Set qdfCurrent = dbCurrent.CreateQueryDef("qryCurrent", strSQL)
Set rsdCurrent = dbCurrent.OpenRecordset("qryCurrent", dbOpenDynaset)
rsdCurrent.MoveLast

End Function

now i'm stumped - any advice would be very appreciated.
Thanks!

 

[FancyPrairie]

Try using the Count SQL statement. Something like this:

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim lngCount As Long
    
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    
    strSQL = "Select Count(*) AS CountOf blah blah blah;"
    rst.Open strSQL, cnn
    
    MsgBox rst!CountOf
    
    rst.Close
    Set rst = Nothing

 

[VeryNew]

Thanks Fancy Prairie,
Can you tell me what this means
Dim cnn As ADODB.Connection

I know I could just copy it but I'd like to understand it as well.
Thanks so much for your time! I will go try this.

 

[FancyPrairie]

Sorry, I thought you were using Access 2000. After rereading your post, realized you may be using Access 97. Access 2000 recommends using ADO rather than the older DAO, even though it still supports DAO.

The code should look something like this:

Dim dbs as Database
Dim rst as Recordset
Dim strSQL As String

Set dbs = CurrentDB
Set rst = dbs.OpenRecordset("Select Count(*) AS CountOf blah blah blah;&quot

msgbox rst!CountOf

rst.Close

 

[VeryNew]

Thanks so much!