Solved

Return the number of records in a query

Posted on 2014-01-12
8
383 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 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 41

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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now