Getting data from code into a report 1

[chiefman]

I am trying to get some values from my code into a report. Basically I am listing all of the possible values on one side of the report and how many times their "ID code" appears in another table down the other side of the report. When I try to set the value of the text boxes in the report it says that I can't do it. I'm confident that the code works, but I can't get the values where they need to be. I've also tried queries, but they don't seem flexible enough for what I'm doing. If anyone knows of any way to get values from code into a report or knows how to do a query that might work I would appreciate it very much. Thanks in advance!

 

[dhookom]

I don't know why you don't work with a bound report but you can use the Print method to "insert" values almost anywhere you want on a report page. For instance using a blank report in Northwind.mdb

Private Sub Report_Page()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intY As Integer
    Dim strSQL As String
    strSQL = "SELECT CategoryID, CategoryName, Description " & _
        "FROM Categories"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    intY = 1000
    With rs
        .MoveFirst
        Do Until .EOF
            intY = intY + 300
            Me.CurrentY = intY
            Me.CurrentX = 100
            Me.Print .Fields("CategoryID")
            Me.CurrentY = intY
            Me.CurrentX = 1000
            Me.Print .Fields("CategoryName")
            Me.CurrentY = intY
            Me.CurrentX = 5000
            Me.Print .Fields("Description")
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

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]

Thanks. I think that will help. The reason I am not using a bound report is because there are too many things that have to be taken into account. I have to get the description and print that, then I have to see if the ID matches the ID in another table, then I have to check to see if it matches the "cancelled" condition. Then on top of that I will have to later include a date comparison. If you can write a query that will do that, more power to you, but this seems a whole lot more straight-forward to me. Thanks again for the suggestion, though. I appreciate it.

 

[dhookom]

chiefman,
You might want to consider a "hybrid" report that binds your main data. You could use code in the On Format or other event to Print the other stuff. You report's record source would contain the same number of records as your main recordset.

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]

That may be worth a look. Thanks again.