[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Return the number of records in a query

Posted on 2014-01-12
8
Medium Priority
?
392 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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