Get number of records in a subform 
Author Message
 Get number of records in a subform
Access 2000 and SQL 2000

I am building a custom search form. A SQL string is built up dynamically
depending what the user wants to search for, and a subform displays the
result of the search by setting the recordsource to the SQL string as:

Me.sfrmQuickSearch.Form.RecordSource = strSQL

However I also want to know how many records the search has returned. What
would be the best way to do this?

Thanks for any help.

Adrian.



Sun, 03 Jul 2005 20:21:15 GMT
 Get number of records in a subform

I can't remember exactly the correct syntax, but you can try
to add something like this after :

Me.sfrmQuickSearch.Form.RecordSource = strSQL

Dim intRecordCount as integer
intRecordCount = Me.sfrmQuickSearch.Form.RecordCount

Though i am not sure if that is a valid or not, maybe

intRecordCount = Me.sfrmQuickSearch.Form.RecordSetClone.RecordCount

if you can't use the RecordCount property directly off the Form Property,
then you can set a temp recordset, base the recordset to the form's
RecordSetClone and then get the recordcount of the temp recordset.


Quote:
> Access 2000 and SQL 2000

> I am building a custom search form. A SQL string is built up dynamically
> depending what the user wants to search for, and a subform displays the
> result of the search by setting the recordsource to the SQL string as:

> Me.sfrmQuickSearch.Form.RecordSource = strSQL

> However I also want to know how many records the search has returned. What
> would be the best way to do this?

> Thanks for any help.

> Adrian.



Sun, 03 Jul 2005 20:59:04 GMT
 Get number of records in a subform
Serge,

Thanks. I can use some code like this:

******************************************************
Dim rs As ADODB.Recordset
Dim SubformNumRecords As Long

Set rs = Me.sfrmQuickSearchSubFrm.Form.RecordsetClone

With rs
    If rs.EOF Then ' There are no records
        MsgBox "No records found."
    Else
       Dim counter As Integer
          Do While counter < 200
          DoEvents
          counter = counter + 1    ' Increment Counter.
       Loop
        ' To get accurate count we need to go to the last record
        .MoveLast
        SubformNumRecords= .RecordCount
    End If
End With

***********************************************

However although I thought by moving to the last record this would ensure an
accurate count this does not appear to be the case. I therefore had to put
in a loop to add a delay before counting the records. The problem with this
is that the more records returned the longer the delay required, and as one
does not know how many records returned one has to put in a delay to allow
for a large number of records returned (though I will put a limit in the
query). This means that for searches that just return a few records it will
take longer than is neccessary.

Adrian


Quote:
> I can't remember exactly the correct syntax, but you can try
> to add something like this after :

> Me.sfrmQuickSearch.Form.RecordSource = strSQL

> Dim intRecordCount as integer
> intRecordCount = Me.sfrmQuickSearch.Form.RecordCount

> Though i am not sure if that is a valid or not, maybe

> intRecordCount = Me.sfrmQuickSearch.Form.RecordSetClone.RecordCount

> if you can't use the RecordCount property directly off the Form Property,
> then you can set a temp recordset, base the recordset to the form's
> RecordSetClone and then get the recordcount of the temp recordset.



> > Access 2000 and SQL 2000

> > I am building a custom search form. A SQL string is built up dynamically
> > depending what the user wants to search for, and a subform displays the
> > result of the search by setting the recordsource to the SQL string as:

> > Me.sfrmQuickSearch.Form.RecordSource = strSQL

> > However I also want to know how many records the search has returned.
What
> > would be the best way to do this?

> > Thanks for any help.

> > Adrian.



Mon, 04 Jul 2005 16:46:40 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Getting number of record added

2. Get the automatic identity number when creating a new record

3. Changing a large number of records in a database over the Internet

4. Size of tables, Number of records

5. Getting percentages of records in each month

6. How can i retrieve specific number of records ???

7. Count number of records returned

8. DAO: Counting number of records in the background

9. troubles getting the ID after adding a record

10. Identity Auto Number on a New Record

11. Getting unique number

12. Getting a few records from tables


 
Powered by phpBB® Forum Software