Full Version: Counting Specific Records In A Form
UtterAccess Forums > Microsoft® Access > Access Forms
engloy
I have a query that returns the following information:
ields: Name, Working?, Occupation, Married?, Children?, No. of children
Dataset:
John, Yes, Clerk, Yes, Yes, 1
Mary, No, Nil, Yes, Yes, 3
Steve, Yes, Driver, No, No, 0
James, Yes, Lawyer, No, No, 0
Richard, No, Nil, No, No, 0
I am trying to create a form/report that will count for me the following:
Total no. of records (think this one is easy... I just need a text box with "=Count(*)"
No. of people who are employed (ie. where [Working?]="Yes")
No. of people who are married (ie. where [Married?] = "Yes")
No. of people with children (ie. where [Children?] = "Yes")
No. of people with more than one child (ie. where [No. of children] > 1)
How do I create such a report?
Would appreciate your help! Thanks in advance!
DFish
In a form you can use the recordsetclone and walk through the records and count the ones that have a yes for what you want.
Take a backup and then in the forms on current event put the following Code. You will have to change the field names on the recordset to the correct names of your fields.
CODE
  
Dim rs as DAO.RecordSet
Dim I as Interger
Dim rsWork as Interger
Dim rsMarried as Interger
Dim rsChildren as Interger
Dim rsNoChild as Interger
Set rs = Me.RecordSetClone      'set rs to the forms recordsource
If rs.eof = false then                  'Checks for records
      rs.MoveFirst
      For i = 0 To rs.RecordCount - 1         'Loops through all records
                 if rs.Working = Yes then         'checks if current record is employed and increments count
                        rsWork = rsWork + 1
                 End If
                 If rs.Married = Yes then            'checks if current record is Married and increments count
                        rsMarried = rsMarried + 1
                 End If
                 If rs.Children = Yes then            'checks if current record is Has children and increments count
                         rsChildren = rsChildren + 1
                         If rs.NoOfChildren > 1 then        'checks the number of children
                                  rsNoChild = rsNoChild + 1
                         End if
                 End If
                 rs.MoveNext                    'set next record
       Next i
End If              
'you need to change the next lines to work with your form text boxes to hold the totals
Me.YourEmployedCount = rsWork
Me.YourMarriedCount = rsMarried
Me.YourChildrenCount = rsChildren
Me.YourNoChildCount = rsNoChild
rs.Close             'Closes and cleans up
Set rs = Nothing

Please make a backup before you use
Hope this helps
engloy
Hi,
Thanks for the reply.
I think the code that you've given me should work great (after changing "Interger" to "Integer" frown.gif
However, I keep getting this error with regards to this statement:
Dim rs as DAO.RecordSet
"Compile error: User-defined type not defined"
I have tried changing it to Dim rs as RecordSet but the code doesn't seem to work after.
Any idea what else I should change to debug?
Thanks for the great help!
danishani
When in VBA modus, goto Tools > References >
ick the Microsoft DAO 3.6 Object Library
HTH
Daniel
engloy
Thanks Daniel! That helped.

Further problems though... With the following lines

CODE
      For i = 0 To rs.RecordCount - 1         'Loops through all records
nbsp;                if rs.Working = Yes then         'checks if current record is employed and increments count
                        rsWork = rsWork + 1
                 End If


I'm getting the ".Working" highlighted in the debugger with the message "Compile error: Method or data member not found". I have checked and "Working" is a valid field in my query.

What should be the correct syntax?

Thanks for the great help!
Edited by: engloy on Wed Oct 27 4:13:19 EDT 2004.
Chaga
Replace rs.Working (and all other field names) with rs!Working.
HTH
DFish
Sorry for the code mistakes.
idn't check the spelling and code marks
Hope it worked out after the corrections that Chaga and danishani gave above
engloy
Thanks DFish, Chaga and danishani for the great help!
Fish, the code works great except that the textboxes don't consistently display the correct count number unless I specify to jump to a new record when the form is opened. Not sure why that is the case, but I have a created a macro to jump a new record whenever the form is opened so that the textboxes reflect accurately the correct counts.
DFish
The counts aren't right because the recordset is populated correctly just add movelast under the first IF statement. Like the following to populate the recordset.
CODE

If rs.eof = false then                  'Checks for records
         rs.MoveLast
         rs.MoveFirst
...................

That will populate the recordset and you will get the correct numbers
Sorry about the errors
Good Luck
engloy
That works great DFish! Thanks a million...
One last question... So far I've managed to make the code work on a form by entering it in the On Current event. However, there's no On Current event for a report. How do I apply the code to a report too?
Thanks once again. You've been a great help!
DFish
In a report you can use the On Format property of the section that you want the totals to show on. You will probably use the report footer or detail footer to show the totals. You can also count the records by using the summary report options in the report wizard.
Hope this helps
engloy
Hmm...
tried putting the code in the On Format property of the report footer but I get the error message "Method or data member not found" with ".RecordsetClone" highlighted in the "Set rs = Me.RecordsetClone" line.
Any ideas on how to solve this?
Thanks again!
DFish
Ok I did not know that reports would not take the recordsetclone property.

We will have to open a recordset

at the top op the sub above where you have Dim rs As DAO.Recordset put the following

Dim db As DAO.Database


Then right above where you set the recordsetclone put the following

Set db = CurrentDb


Then replace the Recordsetclone statement with the following


Set rs = db.OpenRecordset("YourQueryHere", dbOpenDynaset, dbSeeChanges)

Change the "YourQueryHere" with the Name of the table\query you are using for your report.

then at the bottom of you sub where it has Set rs = Nothing Put the following

db.Close
Set db = Nothing

That should Do It.

However if you are opening this report from the form that the totals are on you could just make the textboxes on the report = the textboxes on the form. Use the following statement in the Control source of the textbox on the report

=Forms!YourFormName!YourFormTextboxName

Change the Names to reflect your form name and textbox name. That will make the report textbox = the forms textbox. However the form has to be open when the report runs or it wont work. But it does keep you from having all the code in the report.

Sorry I did'nt know about the report not taking the recordsetclone as a property (learn something everyday)

Good Luck
Edited by: DFish on Wed Oct 27 13:53:11 EDT 2004.
EFCoins
FullSQL = "SELECT COUNT(*) AS Countrec " & _
"FROM queryname" & _
"WHERE field= " & fieldvalue
Set dbs = CurrentDb
Set recset1L = dbs.OpenRecordset(FullSQL)
CountL = recset1L.Fields("CountRec")
CountL is the number of records with the field value that you want
engloy
Thanks DFish again for your help.
One problem with this line,
Set rs = db.OpenRecordset("YourQueryHere", dbOpenDynaset, dbSeeChanges)
Oget a Too Few Parameters error after I have replaced YourQueryHere with my query name. I have tried to put the query name between square brackets to see whether if it helps. Instead, I get a Run-time error '3078': The Microsoft Jet database engine cannot find the input table or query '[YourQueryHere]'. Make sure it exists and that its name is spelled correctly.
Any ideas how to remedy this?
DFish
Try using an SQL statement instead of the query.
Select * From YourTableName"
Again replace the YourTableName with the name of your table.
Try this and see if it works.
Hope this helps
engloy
Hmm... still no go :(
How the code runs... but I get the wrong values displayed in my report.
Osuppose the other solution I might possibly have (if I can't get the code to work) is to print the form instead and format the form like a report.
Of course, the other option would be to try the "open report based on the form's values" trick frown.gif
DFish
I posted this earlier and might work. Sorry the code doesn't work.
If you are opening this report from the form that the totals are on you could just make the textboxes on the report = the textboxes on the form. Use the following statement in the Control source of the textbox on the report
=Forms!YourFormName!YourFormTextboxName
Change the Names to reflect your form name and textbox name. That will make the report textbox = the forms textbox. However the form has to be open when the report runs or it wont work. But it does keep you from having all the code in the report.
Good Luck
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.