GENERATE report/query on the fly...

[ineedahelp]

I have included a snip of my code. I am trying to output my results to a report or a query. Right now I can see my results in the immediate window due to my DEBUG command. how can I take these results and create a report/query on the fly? Thanks in advance for any help...its probably simple and I just can't see it!!
.........

Do Until rs2.EOF
Do Until rs1.EOF
If rs2!Symbol = rs1!Symbol Then
If rs1!MarketPrice <> -5.25 Then
Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
'rs1.MoveNext
Exit Do
Else
rs1.MoveNext
End If
Else
rs1.MoveNext

[NeoPa]

Not only can you not push these results to a Form or Report, you can't access the underlying data of a Form or report either.
I hope I'm wrong on this because, like yourself, I feel it should be possible.
I've been trying on and off for years though :-(

Someone contradict me please.

[ineedahelp]

Am I let with just writing them to a temporary table and then using this table as the recordset of my report?

[MMcCarthy]

You need something like this:

 
Public Function writeTextFile()
Dim fhandle1 As Integer
Dim fline As String
    'open the text file to be written to
    fhandle1 = FreeFile
    Open "Full path to text File" For Output Access Write As #fhandle1
 
    Do Until rs2.EOF
        Do Until rs1.EOF
            If rs2!Symbol = rs1!Symbol Then
                If rs1!MarketPrice <> -5.25 Then
 
                    fline = CStr(rs1!LocateDate) & ", " & rs1!Symbol & ", " & CStr(rs1!MarketPrice)
                    Print #fhandle2, fline
                End If
            End If
            rs1.MoveNext
        Loop
        rs2.MoveNext
    Loop
 
    Close #fhandle1
 
End Function
 
 

[Andrew Thackray]

From the code snippet it loks like you can do this by creating an SQL statement & setting the report recordsource or the queries command to the SQL

The code would look something like this

[code]

dim SQL as string
dim cMarketPrice as currency

cmarketprice = 5.5
SQL = "Select LocakDate Symbol,MarketPr ice from Table1 inner join Table2 on Table1.Symbol = Table2.Symbol where Table1.MarketPr ice = " & cMarketPrice & " ;"

Reports!"Report Name").recordso urce = sql

[code]

I use this technique of creating SQL in code and setting recordsources etc to the SQL a lot when I want to format data on the fly.

you could include this code in a routine where you pass the market value & create a report/query for that market value

[Andrew Thackray]

I forgot to mention that if you only want specific records to appear in the report that are selected programmaticall y use the IN keyword and insert the record keys into a list for the IN keyword. The code would look like this

[code]

dim SQL as string
dim Pricelist as string
Dim rsTable1 as recordset
Dim CMarketPrice as currency

rstable1.open

cMarketPrice = AValue You want to test for
Do while not EOF
if rsterable1.Mark etPrice = CMarketPrice then
if len(pricelist) > 0 then pricelist = pricelist & ","
pricelist = pricelist & cmarketprice
endif
rstable1.movene t
loop
rstable1.close
cmarketprice = 5.5
SQL = "Select LocakDate Symbol,MarketPr ice from Table1 inner join Table2 on Table1.Symbol = Table2.Symbol where Table1.MarketPr ice = " & cMarketPrice & " and Record1MarketPr ice IN (: & Pricelist & " ;"

Reports!"Report Name").recordso urce = sql

[PEB]

Hi,

If you save your data in a temporary table, than you can just use it in your report... It's a solution also!

But there is something better... You can see what you see on your debug window but directly in Report if you paste your code in Open event procedure of the respective report

And instaed to use
debug.print

try with

Me.Print

Or

ReportName.Prin t

Are you seeking for this? :)

[ineedahelp]

Thank you for the solution of using the OnOpen Event of a report. Just a few questions...I named my report rptLastLSData. when I tried to open the report, I got a error "Variable Not Defined" on the rptLastLSData.p rint line. Also does my report have any recordset defined in the properties of the report? Do I need any fields defined in the report or will it just print out like it does with DEBUG?
thank you for additonal help!

[NeoPa]

Also does my report have any recordset defined in the properties of the report?

rptLastLSData has a RecordSource property (Open the Properties page while designing the report and you should see it at the top). In the OnOpen event procedure you can access this via Me.RecordSource . If you set this to a RecordSource here, then it will requery the report with your new Record Source.

[PEB]

Ok!

Try this code onFormat property in your detail section:

    Dim rpt As Report
    Dim strMessage As String
    Dim intHorSize As Integer, intVerSize As Integer
 
    Set rpt = Me
    strMessage = "DisplayMessage"
    With rpt
        'Set scale to pixels, and set FontName and
        'FontSize properties.
        .ScaleMode = 3
        .FontName = "Courier"
        .FontSize = 24
    End With
    ' Horizontal width.
    intHorSize = rpt.TextWidth(strMessage)
    ' Vertical height.
    intVerSize = rpt.TextHeight(strMessage)
    ' Calculate location of text to be displayed.
    rpt.CurrentX = (rpt.ScaleWidth / 2) - (intHorSize / 2)
    rpt.CurrentY = (rpt.ScaleHeight / 2) - (intVerSize / 2)
    ' Print text on Report object.
    rpt.Print strMessage