troubleshooting Question

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

Avatar of chaticathe
chaticathe asked on
Microsoft Access
4 Comments1 Solution418 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros