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,MarketPrice from Table1 inner join Table2 on Table1.Symbol = Table2.Symbol where Table1.MarketPrice = " & cMarketPrice & " ;"

Reports!"ReportName").recordsource = 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 programmatically 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.MarketPrice = CMarketPrice then
if len(pricelist) > 0 then pricelist = pricelist & ","
pricelist = pricelist & cmarketprice
endif
rstable1.movenet
loop
rstable1.close
cmarketprice = 5.5
SQL = "Select LocakDate Symbol,MarketPrice from Table1 inner join Table2 on Table1.Symbol = Table2.Symbol where Table1.MarketPrice = " & cMarketPrice & " and Record1MarketPrice IN (: & Pricelist & " ;"

Reports!"ReportName").recordsource = 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.Print

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.print 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
 
 

[ineedahelp]

Thank you all for your responses! I am unfortunately, but probably obviously, not as advanced at programming as I wish I was. I will include all my code that works great and outputs in the immediate window just what I want. All I need now is a way to preview these results and possibly allow the user to print them out for hard copy...I thought of creating a report. Does anyone have a solution for me knowing now that I am somewhat a beginner!!??? Thanks!!

Sub FindLast()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
'Do Until rs2.EOF
' Debug.Print rs2!Symbol
' rs2.MoveNext
'Loop
rs1.MoveFirst
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
'Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

[ineedahelp]

Thank you for your suggestions! I have put the code you suggested into the DETAIL_FORMAT event of the report. It works and displays strMessage in the detail section. I now need to assign strMessage the values of rs1.LocateDate, rs1.Symbol and rs1.MarketPrice. How do I do this? I have included my code again below...AND where do I put this code? Somewhere in the report? My report is called rptLASTLSDATA. Thank you for any help!!!

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
'Do Until rs2.EOF
' Debug.Print rs2!Symbol
' rs2.MoveNext
'Loop
rs1.MoveFirst
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
'Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

[PEB]

So
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String
Dim rpt As Report
Dim strMessage As String
Dim intHorSize As Integer, intVerSize As Integer

Set rpt = Me
strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
'Do Until rs2.EOF
' Debug.Print rs2!Symbol
' rs2.MoveNext
'Loop
rs1.MoveFirst
Do Until rs2.EOF
Do Until rs1.EOF
If rs2!Symbol = rs1!Symbol Then
If rs1!MarketPrice <> -5.25 Then
strMessage = rs1!LocateDate & " " & rs1!Symbol & " " & rs1!MarketPrice
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
'rs1.MoveNext
Exit Do
Else
rs1.MoveNext
End If
Else
rs1.MoveNext
'Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing







--------------------------------------------------------------------------------

Thank you for your suggestions! I have put the code you suggested into the DETAIL_FORMAT event of the report. It works and displays strMessage in the detail section. I now need to assign strMessage the values of rs1.LocateDate, rs1.Symbol and rs1.MarketPrice. How do I do this? I have included my code again below...AND where do I put this code? Somewhere in the report? My report is called rptLASTLSDATA. Thank you for any help!!!

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
'Do Until rs2.EOF
' Debug.Print rs2!Symbol
' rs2.MoveNext
'Loop
rs1.MoveFirst
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
'Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

[ineedahelp]

I think this code you sent is working except it prints all my data on one line...do you know how I would "issue a carriagle return"?

[NeoPa]

I think this code you sent is working except it prints all my data on one line...do you know how I would "issue a carriagle return"?

If you're building up a string (or any other printing really) the string to insert or add is VbCrLf.

EG.

Dim strPrt As String
 
strPrt = "ABCDE" & VbCrLf & "abcde"
Debug.Print strPrt

would print :
ABCDE
abcde