Check if query returns Null

[styphon]

Hi, I'm new to VBA coding and self-taught on everything I know. I'm trying to build a simple database for tracking accounts ordered through a third party. I have a Form which opens a Query, but when the query returns nothing I want it to display a MsgBox asking if the user wants to search again. I am using DCount however I am getting a runtime error 2465 with the following code:

Private Sub Form_Load()
    Dim intCount
    DCount intCount, [Queries]![sign_syops]![mt_surname]
    If intCount = 0 Then
        strMsgBox = MsgBox("No results found. Search again?", vbYesNo, "Error!")
        If strMsgBox = 6 Then
            DoCmd.Close acForm, "signed_syops_results", acSaveNo
        Else
            DoCmd.Close acForm, "signed_syops", acSaveNo
            DoCmd.Close acForm, "signed_syops_results", acSaveNo
        End If
    Else
        DoCmd.Close acForm, "signed_syops", acSaveNo
    End If
    
End Sub

I appologise if my code is really bad.

 

[namliam]

Dim intCount
=> Should read Dim intCount as Integer
Allways determain your type when using Dim, in this case Integer

DoCmd.Close acForm, "signed_syops_results", acSaveNo
Form names should always be prefixed with "frm" and queries with "qry" so you can tell on sight what is what. In your code now you cannot see what "signed_syops" is unless you look at the command beeing issued.

Now DCount...
Using the Dcount you cannot count a field... or not like that...
From the HelpCount(Expr, Domain, Criteria)

Expresion is the "field" you want to count, mt_surname I think in this case.
Domain is the table or query, sign_syops in this case
Criteria is any criteria you need, none in this case

So your Dcount should read:
intCount = DCount("mt_surname","sign_syops")

That beeing said... you shouldnt use DCount, or limit its use because it is freaking slow.
Seeing as you are doing this "On load" of a form, perhaps you can use "Me.Recordcount" which will retrieve the count of records for the current form.
If the recordset/query for the form is complex or really big, you may get 1 for having x amoung of records and 0 for (obviously) no records.

 

[styphon]

Dim intCount
=> Should read Dim intCount as Integer
Allways determain your type when using Dim, in this case Integer

DoCmd.Close acForm, "signed_syops_results", acSaveNo
Form names should always be prefixed with "frm" and queries with "qry" so you can tell on sight what is what. In your code now you cannot see what "signed_syops" is unless you look at the command beeing issued.

Now DCount...
Using the Dcount you cannot count a field... or not like that...
From the HelpCount(Expr, Domain, Criteria)

Expresion is the "field" you want to count, mt_surname I think in this case.
Domain is the table or query, sign_syops in this case
Criteria is any criteria you need, none in this case

So your Dcount should read:
intCount = DCount("mt_surname","sign_syops")

That beeing said... you shouldnt use DCount, or limit its use because it is freaking slow.
Seeing as you are doing this "On load" of a form, perhaps you can use "Me.Recordcount" which will retrieve the count of records for the current form.
If the recordset/query for the form is complex or really big, you may get 1 for having x amoung of records and 0 for (obviously) no records.

OK, thanks for the tips. I've made those changes you said. I'm expecting 5-600 records in the main table, with only 2 or 3 results back, maybe up to 10 with popular surnames.

I used intCount = Me.RecordCount and got Compile error: Method or data member not found. The DCount line works but if it's gonna cause issues later on when I have a large number of records in there then why wouldn't Me.RecordCount work?

 

[namliam]

Hmz, OK Me.recordcount dont work... somehow I was expecting it to...

There must be some me. you can use, but my brain seems to be fried for the moment...

Try this:
dim rs as dao.recordset
set rs = me.recordsetclone
incount = rs.recordcount

 

[styphon]

Hmz, OK Me.recordcount dont work... somehow I was expecting it to...

There must be some me. you can use, but my brain seems to be fried for the moment...

Try this:
dim rs as dao.recordset
set rs = me.recordsetclone
incount = rs.recordcount

That works fine, thank you. Exactly what I was after.