Counting Total Number of Returned Results from Query

[justinpatters]

Hello,
I'm trying to run a line of code after doing a Query Search that reports back the total number of results pulled from the search. I've dug around and found some useful code, but nothing that gives the results I'm needing. Most everything gives me the total number from the query. I'm also using a Split Form.

The process goes:

• Enter numeric search in Text10 and hit the search button to run the following macro: [studentID]=[Forms]![Query1]![Text10]
  
• It then gives me the requested information.
  
• I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.

Can I simply add an additional line to the Macro or does anyone have a suggestion? Thanks!

 

[Minty]

I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.

Can I simply add an additional line to the Macro or does anyone have a suggestion? Thanks!

Can I be confused - if you don't want the total for the studentID you've searched for or the total number of records in the query, what are you trying to count?

 

[justinpatters]

Can I be confused - if you don't want the total for the studentID you've searched for or the total number of records in the query, what are you trying to count?

Hi Minty,

Maybe I've explained it wrong. So if there are 13 items in the database, and 3 of them share unique studentID numbers, the search brings all three of them into the split form. I need a text box to also say that the total number equals 3.

Does this help?

I have the search and data pull working fine, but I cannot find a way for the number of results to be counted. The most I can do is count the total number searched, not the total number found.

 

[Minty]

Okay you should be able to use the DCount function to count the same records you have just selected.
http://www.techonthenet.com/access/functions/domain/dcount.php

 

[justinpatters]

Thanks, Minty.

So when I put that in,

Dim LTotal As Long

LTotal = DCount("studentID", "Query1")

Text19 = LTotal

It gives me the total number of records searched with a studentID, not the total pulled into the database that match my search. Any ideas?

 

[Minty]

Dim LTotal As Long

LTotal = DCount("studentID", "Query1")

Text19 = LTotal

You are close but you haven't added your criteria

LTotal = DCount("studentID", "Query1", "[studentID]= " & [Forms]![Query1]![Text10])

This assumes that your StudentId is a number value.

 

[justinpatters]

You are close but you haven't added your criteria

LTotal = DCount("studentID", "Query1", "[studentID]= " & [Forms]![Query1]![Text10])

This assumes that your StudentId is a number value.

Got it! Thanks so much!

Here's the following code for the button release if anyone is looking for it:

Dim LTotal As Long

LTotal = DCount("studentID", "Query1", "[studentID]=[Forms]![Query1]![Text10]")

Text19 = LTotal

 

[Minty]

As a follow up - and I realise you are right at the start of this - Get into the habit of naming your controls / forms / queries etc with something more meaningful.

In six months time [Forms]![Query1]![Text10] isn't going to mean a thing to you.
[Forms]![frmSearch]![txtFindStndID] or similar might give you a good clue.

Have a look here http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention for some wisdom on the subject.