RecordCount

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Goal:

Run a query and count the resulting records. If the
resulting records = 0, then I simply want a message box to
pop up, however, it the records are > 0, then I want to
print the results. Below is the code I have attempted to
write, however, I do not think it is counting the
resulting records as the message box pops up when the
records are > 0. Please help!

Sub Duplicates()


Dim stDocName5 As String
stDocName5 = "15th_Find Dups in Terms and Changes with
Point Loss Tbl"
DoCmd.OpenQuery stDocName5

If RecordCount = 0 Then
MsgBox "No Duplicates Found"

Else: DoCmd.PrintOut stDocName5, acPrintAll
End If

End Sub
 
Jeff,
If you added Option Explicit at the beginning of the code module, compiler
would detect your mistake.
You created a variant variable called RecordCount and its value is Null by
default.
What you should do instead is create a recordset type variable, open
recordset and check its RecordCount property, ie

dim rs as recordset
set rs=currentdb.openrecordset("qryname")
if rs.recordcount<>0 then
....
 
DoCmd.OpenQuery stDocName5
Click to expand...

1) Always get the db engine to do the work:
2) Datasheets suck! Use a proper report and print that

If DCount(stDocName5)=0 Then
' Nothing to do....
MsgBox "There are no duplicates"

Else
' Okay, print them out
DoCmd.OpenReport "NeatDuplicateReportBasedOnQuery"), _
acViewNormal, stDocName5

End If

3) If you want to look at the RecordCount property, then you need to open a
proper recordset, and load the whole thing into memory with a .MoveLast
before the count is updated correctly. Slow and bad!

4) Even smarter thing is just to open the report and use the On_NoData
event to tell the user something intelligent and close again. Check help
for Events On Reports.


Hope that helps


Tim F
 
-----Original Message-----
Goal:

Run a query and count the resulting records. If the
resulting records = 0, then I simply want a message box to
pop up, however, it the records are > 0, then I want to
print the results. Below is the code I have attempted to
write, however, I do not think it is counting the
resulting records as the message box pops up when the
records are > 0. Please help!

Sub Duplicates()


Dim stDocName5 As String
stDocName5 = "15th_Find Dups in Terms and Changes with
Point Loss Tbl"
DoCmd.OpenQuery stDocName5

If RecordCount = 0 Then
MsgBox "No Duplicates Found"

Else: DoCmd.PrintOut stDocName5, acPrintAll
End If

End Sub
.
Quick and dirty set recordsource of report to the query
Click to expand...
on open event
if me.recordsetclone.recordcount=0 then
msgbox stDocName5,vbcritical,"No Records"
docmd.close acreport "XXX"
docmd.hourglass false
exit sub
end if

or use has data event
 
Back
Top