Solved

Return the number of records in a query

Posted on 2014-01-12
8
390 Views
Last Modified: 2014-01-16
I'm creating a search page that just returns the results of a search and displays them on a report that I have embedded in a form as a sub-report. The sub-report runs a query with the search term parameters (there are 3, if 1 or 2 are left blank, I programmatically change them to * and proceed with running the query.

The problem:
When there are no records returned, I want to display a message that says "No records to match your criteria, try again" I can't seem to find a way to get a record count or a no records returned out of a query??? I've tried putting code in the "no data," event of the report, I've tried opening a query recordset (which I'm not sure I'm doing correctly, as I've only used recordsets with tables in the past) I can't figure this out.

Can anyone help with solution?
0
Comment
Question by:Mavislee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39775337
Putting code in the OnNoData event usually works. Could we see the code that you put there?

Your Query Recordset should also do it. One again, could we see the code?

Another way to do that, since you seem to be using VBA, is to call the DCount method with exactly the same criteria as the one used by the subreport. This will tell you how many records will be returned by that criteria.

And reading your question carefully, what do you mean by "a report embedded in a form". Is this a new feature in more recent versions of Access? I am still in 2003 and as far as I know after having used Access for 20 years, you cannot embed a report in a form. Only SubForms are allowed.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39775351
If the query does not take long time then you may execute it twice:
1)  SELECT COUNT(*)  FROM YourTable WHERE YourConditions   ... returns number of rows
2) SELECT field1, field2, ...  FROM YourTable WHERE YourConditions  ... returns data rows
0
 

Author Comment

by:Mavislee
ID: 39775695
Hi James, I'm just getting this and it's late. I will put the code uphow tomorrow. As for reports embedded, I'm probably using the wrong terminology. I'm using access 2010 and I created a report and dragged it onto a form. Similar to a subform, only it's a report. It works great for what I'm developing (a phone directory). Not sure if that can be done in previous versions.

Pcelba, it's not a long query and I can execute it twice, but I'm not sure how your solution will help. How do I get a record count out of that? I apologize in advance as I haven't developed anything in many years and I'm just getting back to it again.

 Thanks so much to both of you for responding!
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 30

Expert Comment

by:hnasr
ID: 39776160
To reduce our assumptions, upload a sample database. Tell what to expect.
Report not to display?
Just a message is displayed?
....

One way:
Have an unbound field on main from say, TextS, and the same on Subreport.
Set TextS on Subform =1
Link report control with master and child fields as TextS.

The search process set TextS on mainform to 1 if dataexists, 0 if not.
Refresh displays the found records.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39777244
You can use my CreateAndTestQuery function to return the number of records in a query.  Here is some sample code that uses it, followed by the function:

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      strPrompt = "No records found; canceling"
      strTitle = "Canceling"
      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
      GoTo ErrorHandlerExit
   Else
      'Use this line if you need a recordset
      Set rst = dbs.OpenRecordset(strQuery)
   End If

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 11-May-2013

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0
 

Author Comment

by:Mavislee
ID: 39777359
Hi everyone. I am at work today and don't have the db with me. I can prepare a sample tonight if you still need it. In the interim, I'm attaching 3 screen shots and some code in the hopes it will clarify. The code is from a previous iteration, but it's basically the same. The user enters some search criteria (up to 3 fields) I add * to any left out. Click on search, and a query is run and results are displayed in the report. The report is used for display only, there's no interaction with the user so it's not connected with master/child fields as a subform would be.  The form is just a container for the controls and to hold the report.It all works great with the exception of not being able to tell when there are no records that match the criteria. I've included the module code I tried, but it didn't work. I think I put the code to call the module in the "search form code" just under the requery. It didn't work so I took it out.  Looking at this setup, is there a way to take the criteria, run the query, and if there's no records found return a message to the user stating that "There are no records matching criteria?" I would think this is such a basic thing, but I just can't figure it out.....Thanks so much for all your help!
sample-form.png
design-view.png
Objects.png
Code-for-Sample.docx
0
 

Author Comment

by:Mavislee
ID: 39777381
Thanks Helen. I will test it tonight!
0
 

Author Closing Comment

by:Mavislee
ID: 39787476
I wasn't able to use any of the solutions in their entirety, but I looked around a little more and found the DCount method was the simplest to implement. I placed the below code in the search buttons click event and it works great! Thank you so much everyone, I appreciate all your help!!
 Me.sub_rpt_StaffPhoneList.Requery
   
    RecCount = DCount("*", "qry_staffphonelist")
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question