?
Solved

Return the number of records in a query

Posted on 2014-01-12
8
Medium Priority
?
395 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
8 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 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 43

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

862 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