We help IT Professionals succeed at work.

Report in MS Access 2007 generates multiple pages instead of one.

chaticathe asked
Last Modified: 2012-02-15
I developed a report in MS Access 2007 that generates a summary matrix of all records in the database (12 records).  The matrix is shown correct, but it generates a page for each of the records that are in the database.  What can I do to get the report to generate the matrix only once.  The code I am using is shown below.  I will also attach the report showing the matrix it generates (12 pages).

Private Sub Report_Load()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim x, y, ID As Integer
Dim temp_conseq_value As Integer
Dim temp_like_value As Integer
Dim varconversion As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select R_Number, Consequence_Value, Likelihood_Value, status from RiskData order by consequence_value, Likelihood_value, r_number")
    If rst.RecordCount = 0 Then
        MsgBox ("No records found")
        ID = 1
        temp_conseq_value = rst!Consequence_Value
        temp_like_value = rst!Likelihood_Value
        Do While Not rst.EOF()
          While rst!Consequence_Value = temp_conseq_value And rst!Likelihood_Value = temp_like_value
            If rst!Status <> "Closed" Then
                x = rst!Consequence_Value
                y = rst!Likelihood_Value
                varconversion = Val(Mid(rst!R_Number, 3, 3))
                Me("t" & x & y & ID) = varconversion
                Me("t" & x & y & ID).BackColor = RGB(0, 0, 0)
                Me("t" & x & y & ID).Visible = True
                ID = ID + 1
            End If
           If rst.EOF() Then
            Exit Sub
            End If
         temp_conseq_value = rst!Consequence_Value
         temp_like_value = rst!Likelihood_Value
        ID = 1
       If rst.EOF() Then
        Exit Sub
        End If
    End If

End Sub
Watch Question

MIS Liason
Most Valuable Expert 2012
This one is on us!
(Get your first solution completely free - no credit card required)
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

What does the code above do? Does it create a temporary table? And is your report based on that table?

If it creates a temp table, does the temp table contain the data you expect it to contain?

If you're basing your report on that temp table, and the temp table contains 12 records, then the report will contain 12 detail sections (which would perhaps be your 12 pages).

If you're trying to show a chart of some sort, you should consider using the charting features of Access. Those are designed to show data in the format you're describing.


I removed the record source and it fixed the problem.  thank you so much!  I greatly appreciate it.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Thanks, but as a follow up can you post a "Generic" sample of this database, that illustrates what you have done here...?

As a person who loves Access reports, your "Matrix" looks intriguing...



Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.