Full Version: Obtaining RecordCount in a Subform
UtterAccess Forums > Microsoft® Access > Access Forms
swdeveloper
What method should be called to determine how many records there are being displayed in a subform?
I am trying to find an approach using just the forms - not ADO, DAO, etc...
schroep
Add a textbox control to the main form, and use the following as the controlsource for that textbox:

=nameofsubformcontrol.Form.RecordsetClone.RecordCount

Just a note, this is using DAO. No way around that, other than possibly to use a DCOUNT, which would be far slower.
blankel
Child.Form.RecordsetClone.RecordCount possibly
swdeveloper
Found this out just now too. Guess I should have done a little more digging. In case anyone is needing to do this and doesn't want to concern themselves with ADO, DAO, etc..., connection strings, and whatnot, use the following bit of code.
orms![frmName].SubformName.Form.RecordsetClone.RecordCount
For for a main form and not a subform use:
Forms![frmName].Form.RecordsetClone.RecordCount
alternatively you could use:
Me.Form.RecordsetClone.RecordCount
I hope this helps someone...
ande8150
Thanks to all for the solution.
How would I do a conditional count of records in the subform with the number of records that meet a certain criteria?
For example, how many records have a check in a checkbox?
swdeveloper
A quick way of doing this would be using the following

'Delcare variables
Dim lCount As Long
Dim lChecked as Long

'This is what we will use for our criteria search. -1 is how Access defines that is a box is checked (alternatively, 0
'is used to symbolize that the box isn't checked).
lChecked = -1

'Reset the counter.
lCount = 0

'This checks to make sure there are actually records in the subform.
If Forms![frmName].[SubformName].Form.RecordsetClone.RecordCount >= 1 Then

'A for loop that starts at 1 and moves until the end of the subform.
For i = 1 to Forms![frmName].[SubformName].Form.RecordsetClone.RecordCount

'Set the SelTop property, which refers to each record in the recordset.
Forms![frmName].[SubformName].Form.SelTop = i

'Check to see if the current record's field value matches our criteria.
If Forms![frmName].[SubformName].Form!CheckedBoxFieldName = lChecked Then lCount = lCount + 1

'Tell the Operating System to allow other processes to run while in the loop.
DoEvents

'Increases i by 1.
Next i

'Exit the If statement.
End If

MsgBox "The program found " & lCount & " instances of the criteria in the selected subform."
ande8150
Thanks, but doesn't this require a button press? I was hoping for it to sit in a textbox control box like the record count above.
schroep
Take a look at the DCOUNT function.
ande8150
Thanks Peter, works great. For others, here is the code I am using:
CODE
  
  =DCount("[ReservationID]","EventAttendanceQuery","[Events_EventNumber] = " & Forms!Events!EventNumber & " And [Attended] = -1")

This counts the number of Records in the subform that have a Yes (-1 as is stored in Access) in the Attended field.
Substitutes:
ReservationID = The Primary Key Field of the query (or some other field that you know always containts a value)
EventAttendanceQuery = The name of the Query that sits in the Subform
Event_EventNumber = The linking key on the subform / query
Forms!Events!EventNumber = The linking key on the main form
Attended = Field name with Yes/No format
schroep
In Access, TRUE or YES is stored as a -1. In SQL Server, however, it is stored as a 1.
o ensure things still work should you ever upsize your backend tables, I would write this as:
=DCount("[ReservationID]","EventAttendanceQuery","[Events_EventNumber] = " & Forms!Events!EventNumber & " And [Attended] <> 0")
Since FALSE/NO always = 0.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.