We help IT Professionals succeed at work.

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

chaticathe
chaticathe asked
on
414 Views
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")
    Else
        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
            rst.MoveNext
           If rst.EOF() Then
            Exit Sub
            End If
       
         Wend
         temp_conseq_value = rst!Consequence_Value
         temp_like_value = rst!Likelihood_Value
        ID = 1
       Loop
       If rst.EOF() Then
        Exit Sub
        End If
    End If

End Sub
Risk-Summary-Matrix.pdf
Comment
Watch Question

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

Commented:
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.

Author

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

Commented:
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...

;-)


JeffCoachman

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.

OR

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.