Counting Query Results in VB Code

[Guest]

I need to count the number of results of a query in VB Code. I'd like my
code to look something like this:

if numresults(query1) = 0 then
msgbox "You are missing information. Please fill out the form completely."
else
'Open mail merge document
Application.FollowHyperlink "c:\letters\letter1.doc"
end if

Any suggestions on how to do this WITHOUT the DCount function?

Thanks!

Nick

 

[Guest]

Try:

Public Sub testGetQryCount()

If (getQryCount("query1")) = 0 Then
MsgBox "You are missing information." & vbCrLf & _
"Please fill out the form completely.", vbCritical + vbOKOnly, _
"Cannot Continue!"
Else
'Open mail merge document
Application.FollowHyperlink "c:\letters\letter1.doc"
End If

End Sub


Public Function getQryCount(sQryName As String) As Long

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim fOpenedRecSet As Boolean

Set recSet = CurrentDb().OpenRecordset("SELECT COUNT(*) " & _
"FROM " & sQryName)

If (Not (recSet.BOF And recSet.EOF)) Then
getQryCount = recSet.Fields(0).Value
Else
getQryCount = 0
End If

CleanUp:

If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If

Set recSet = Nothing

Exit Function

ErrHandler:

MsgBox "Error in getQryCount( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Function

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

You can use the Dcount

if Dcount("*","queryName") = 0 then
msgbox "You are missing information. Please fill out the form completely."
else
'Open mail merge document
Application.FollowHyperlink "c:\letters\letter1.doc"
end if

 

[Guest]

Please ignore my post, I should have read the last line, sorry
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[Guest]

Exactly what I was looking for! Haha, shoulda expected that you'd be the one
with the answer.

Thanks again Gunny!

Nick