Solved

count number of records in a sub-report

Posted on 2003-10-28
10
839 Views
Last Modified: 2012-06-21
How can I count the number of records in s sub-report and use it to write a title ( or not if there is no one in the sub-report).
0
Comment
Question by:lbeauvais
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 250 total points
ID: 9636350
Put this code in OnOpen event of the report.

Dim rs 'As Dao.RecordSet
Set rs=CurrentDB.OpenRecordSet(Me!sub-report.Report.RecordSetClone)
If rs.RecordCount>0 Then
rs.movelast
lblTilte.Caption="Number of records are: " & rs.RecordCount
Else
lblTilte.Caption="Zero records."
End IF

Mike
0
 

Author Comment

by:lbeauvais
ID: 9636915
Thank a lot Mike,

I am not very hot with VBA code so If you can help me again with that:

If my report name is : GlobalReport
And ny sub-report name is : Subreport

And I if  put the code "Set rs=CurrentDB.OpenRecordSet...
in the OnOpen event of the GlobalReport

What would be the rest of the code: Set rs=CurrentDB.OpenRecordSet...???

And ifI put it in the OnOpen event of the Subreport, what would it be ???

Thank again!
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9636938
Re:>GlobalReport

Do you have one report called GlobalReport?  Or, you have many reports defined by GlobalReport variable?

Mike
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:lbeauvais
ID: 9637097
I have just one report named "GlobalReport" and a sub-report in it, named "SubReport".
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9637286
Put this code in OnOpen event of the GlobalReport.

Dim rs 'As Dao.RecordSet
Set rs=CurrentDB.OpenRecordSet(Me![sub-report].Report.RecordSetClone)
If rs.RecordCount>0 Then
rs.movelast
lblTilte.Caption="Number of records are: " & rs.RecordCount
Else
lblTilte.Caption="Zero records."
End IF

Mike
0
 

Author Comment

by:lbeauvais
ID: 9637367
Thank, Your very kind to try to help me!

But I'm sorry it doesn't work!

It gives me an error message like:

Impossible to find 'sub-report' field you refered in your expression. (translate from french)

(I have a french Access 2000 but normaly it is the same code as the english one).

Do you think that could make the bug?


0
 

Author Comment

by:lbeauvais
ID: 9637368
Thank, Your very kind to try to help me!

But I'm sorry it doesn't work!

It gives me an error message like:

Impossible to find 'sub-report' field you refered in your expression. (translate from french)

(I have a french Access 2000 but normaly it is the same code as the english one).

Do you think that could make the bug?


0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9639570
I am will offer another solution.  We will start without criteria first therefore the number will be high to begin with.  After adding a criteria, we should get right answer.

In the OnCurent event of the subform, include:

txtNoOfItems="Quantity: " & Nz(DCount("*","TableName"),0)

txtNoOfItems is a text box on your subform where you want to display quantity.
TableName is table where you want to count number of record.

Now, if this works, it will show a very large number (including all items).  We need to add a criteria to limit it to what we need.

Later on, we will cahnge the code to:

txtNoOfItems="Quantity: " & Nz(DCount("*","TableName","CustID=" & Parent!CustID),0)

Please answer to these questions:

Q1: Do you have a text box/field in the main report like CustID to be used to limit quantity?
Q2: Do you have possibly more than one criteria to be used?
Q3: What is variable type of each citria (string, number, date)?

Mike
0
 

Author Comment

by:lbeauvais
ID: 9643813
Hi Mike,

It will probably be good, but you already gave me the clue to find the solution by myself.
Here is what I did (may be it could help some one else):

I put a hided independent textbox named “DetailsInst” in the under form of the report.
(I tried to put it in the GlobalReport but it does not work, I don’t know why)

In the OnOpen event of the GlobalReport,  I  put this code to verify if there is at least one record in the table used to fill the SubReport, and then fill the “DetailsInst” textbox with the title I want:

    Dim rs As DAO.Recordset
    Dim DB As Database
    Dim IDentificateur As Long
    Set DB = Application.CurrentDb
    Set rs = DB.OpenRecordset("tblInstalsoum", dbOpenDynaset)
   
    IDentificateur = Form_frmSoumissions.IDSoumission
    rs.FindFirst "IDSoumissionInstalSoum = " & IDentificateur
   
    If rs.NoMatch = True Then
        Form_frmSoumissions.DetailsInst = ""  ‘(I don’t want a title if there is none)
    Else  ‘(I want this title if there is at least one record
        Form_frmSoumissions.DetailsInst = "Detail de l'installation"    
   End If
   
    rs.Close
    Set rs = Nothing
    Set DB = Nothing

Then, in the GlobalReport, I put a textbox with this control source :
    =[Forms]![frmSoumissions]![DetailsInst]

And it works!

I know that there is probably a more simple way to do it but the important for me is that it works now.

Thank you very much for the time you took helping me.

Excuse my English I’m a French people.

Regards,

Lucie
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9644340
I am glad you found a solution.  Please remember not to award points until you get a workable answer,

Take care,

Mike
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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