Report line troubles

[chiefman]

I'm doing a report where all the values from one table are supposed to show up down the left side of the page and the number of instances of each value is supposed to show up to the right. The problem that I'm having is that when the report goes to display the next value and number of instances it overwrites the previous one instead of putting it on the next line. So all I get is the last value and number. Does anyone have any ideas? Thanks.

 

[snyperx3]

Do you have the Value from table is in a header that is for the name of the field, and the instances in the details section?


-Pete

 

[chiefman]

No, it's an unbound report. It gets all the values from code.

 

[dhookom]

chiefman,
Why would you not mention your values come from code? This method is a bit unusual and very significant to finding a solution.

Could you share your code? Why not bind your report to a query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[chiefman]

I mentioned it in a different thread, sorry. I guess I forgot to include that in this one.

Here's the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim y As Integer 'each subscript in the array
Dim Specialties(30) As String 'types of specialties
Dim SpecCodes(30) As String 'code for each specialty
Dim tmpCount As Integer 'to count the number of records in a given condition
Dim tmpCode As String 'the current code for comparison
Dim rstReports As New ADODB.Recordset
Dim rstReports2 As New ADODB.Recordset

y = 0
tmpCount = 0
tmpCode = ""

rstReports.Open "SELECT SpecialtyCode,SpecialtyDesc FROM SpecialtyInfo", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rstReports2.Open "SELECT * FROM PerMonInput", CurrentProject.Connection, adOpenDynamic, adLockReadOnly

rstReports.MoveFirst
rstReports2.MoveFirst

With rstReports

Do While Not .EOF
For y = 1 To 30
rstReports2.MoveFirst
SpecCodes = ![SpecialtyCode]
Specialties = ![SpecialtyDesc]

Do While Not rstReports2.EOF
tmpCode = rstReports2![Surgical_Specialty_ID]
If tmpCode = SpecCodes And rstReports2![Cancelled_Cases] = "Yes" Then
tmpCount = tmpCount + 1
If Not rstReports2.EOF Then
rstReports2.MoveNext
Else
rstReports2.MoveLast

End If
Else
rstReports2.MoveNext

End If

Loop

Me.Text8 = Specialties
Me.Text10 = tmpCount
tmpCount = 0
.MoveNext
If .EOF = True Then
Exit Sub
End If
Next y
Loop

End With

End Sub

I'm not sure if that's even the best way to go about it. It works, though, except for overwriting the previous value. Any suggestions would be appreciated. Thanks.

 

[dhookom]

I think my response in your other thread might help. The code I provided doesn't use any text boxes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]