Passing data to a report

[galorin]

I'm needing to start creating printable reports, but because I have written most of my database app in VBA and MySQL, I'm not quite sure how to get a report based on a query that I've put together in MySQL. Is there a way of using an ADODB style recordset to populate a report?

 

[Remou]

I have not tried this with reports:
How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/EN-US/

 

[galorin]

Close, I have found a few other resources that can aid in filtering my results. As it stands it looks like I will need to create unfiltered record sets as views on the MySQL server, then have various means of selecting what data and filtering is available for each of my 12 requested reports.

 

[galorin]

I found this

http://www.fontstuff.com/access/acctut19.htm

for filtering sets of results, but some of the reports want different grouping. Would I need to open a different report with the same info based on the requested grouping, or can I programatically set that as well?

Thanks in advance

 

[Remou]

It can be a problem because of footers and so forth:

http://www.tek-tips.com/viewthread.cfm?qid=1390567

 

[galorin]

That's a pretty good link, thanks. I'll try some code out, and if it doesn't work out as expected, and I can't figure out why, I'll get back to you. Thanks.

 

[galorin]

Here's some code that I have in a few places, it changes a bit based on what info I want to display, and I apologise for the lack of elegance.

SQLout = "SELECT h.ht_id, h.softver, h.House_type"
SQLout = SQLout & " , b.ShortCode AS builder_ShortCode"
SQLout = SQLout & " , q.design_level"
SQLout = SQLout & " , h.Floor"
SQLout = SQLout & " , h.Revision"
SQLout = SQLout & " , d.ShortCode AS dist_ShortCode"
SQLout = SQLout & " FROM HouseType AS h"
SQLout = SQLout & " INNER JOIN quotations as q on q.quote_id = h.quote_id"
SQLout = SQLout & " INNER JOIN CompShort as b ON b.Comp_ID = q.build_id"
SQLout = SQLout & " INNER JOIN CompShort as d ON d.Comp_ID = q.dist_id"
SQLout = SQLout & " WHERE h.quote_id = " & Me.se
SQLout = SQLout & " and deleted = 0"
SQLout = SQLout & " ORDER BY House_Type"

Call connectDB
Call getData

If Not rst.EOF Then
        rst.MoveFirst
        Do
        'loop through resultset
        f(0) = rst.Fields(0)
        f(1) = rst.Fields(1)
        For I = rst.Fields.Count - 1 To 2 Step -1
         If IsNull(rst.Fields(I)) Then
          f(I) = ""
         ElseIf rst.Fields(I) = "non" Then
          f(I) = ""
         [i]ElseIf I = 6 Then [COLOR=green]'field where integer value of revision is kept[/color]
           If rst.Fields(I) = 0 Then
            f(I) = ""
           Else
            rev = rst.Fields(I)
            f(I) = "=rev-" & Chr(96 + rev) 'Converts integer revision into a letter
           End If[/i]
        Else
        f(I) = "=" & rst.Fields(I)
        End If
        Next I
            field0 = rst.Fields(2)
            [COLOR=green]'field format is version=builder-region=level=housetype=floor=revision=distributor [/color]
            field1 = f(1) & f(3) & f(4) & f(2) & f(5) & f(6) & f(7)
            Me.ListPos.AddItem item:="" & f(0) & ";" & field1 & ""
        rst.MoveNext
        Loop Until rst.EOF
    End If
cn.CLose

I use the above code to automatically generate an identification string based on the info stored in the database. I want to generate this same string for passing to a report. I have an unfiltered version of a similar report that does not contain automatically generated numbers, that is based off of a MySQL view if an example would help.