By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

GENERATE report/query on the fly...

P: 98
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
Oct 26 '06 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,347
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.
Oct 26 '06 #2

P: 98
Am I let with just writing them to a temporary table and then using this table as the recordset of my report?
Oct 26 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
You need something like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function writeTextFile()
  3. Dim fhandle1 As Integer
  4. Dim fline As String
  5.     'open the text file to be written to
  6.     fhandle1 = FreeFile
  7.     Open "Full path to text File" For Output Access Write As #fhandle1
  8.  
  9.     Do Until rs2.EOF
  10.         Do Until rs1.EOF
  11.             If rs2!Symbol = rs1!Symbol Then
  12.                 If rs1!MarketPrice <> -5.25 Then
  13.  
  14.                     fline = CStr(rs1!LocateDate) & ", " & rs1!Symbol & ", " & CStr(rs1!MarketPrice)
  15.                     Print #fhandle2, fline
  16.                 End If
  17.             End If
  18.             rs1.MoveNext
  19.         Loop
  20.         rs2.MoveNext
  21.     Loop
  22.  
  23.     Close #fhandle1
  24.  
  25. End Function
  26.  
  27.  
Oct 27 '06 #4

Andrew Thackray
P: 76
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
Oct 27 '06 #5

Andrew Thackray
P: 76
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
Oct 27 '06 #6

PEB
Expert 100+
P: 1,418
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? :)
Oct 28 '06 #7

P: 98
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!
Oct 30 '06 #8

NeoPa
Expert Mod 15k+
P: 31,347
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.
Oct 30 '06 #9

PEB
Expert 100+
P: 1,418
PEB
Ok!

Try this code onFormat property in your detail section:

Expand|Select|Wrap|Line Numbers
  1.     Dim rpt As Report
  2.     Dim strMessage As String
  3.     Dim intHorSize As Integer, intVerSize As Integer
  4.  
  5.     Set rpt = Me
  6.     strMessage = "DisplayMessage"
  7.     With rpt
  8.         'Set scale to pixels, and set FontName and
  9.         'FontSize properties.
  10.         .ScaleMode = 3
  11.         .FontName = "Courier"
  12.         .FontSize = 24
  13.     End With
  14.     ' Horizontal width.
  15.     intHorSize = rpt.TextWidth(strMessage)
  16.     ' Vertical height.
  17.     intVerSize = rpt.TextHeight(strMessage)
  18.     ' Calculate location of text to be displayed.
  19.     rpt.CurrentX = (rpt.ScaleWidth / 2) - (intHorSize / 2)
  20.     rpt.CurrentY = (rpt.ScaleHeight / 2) - (intVerSize / 2)
  21.     ' Print text on Report object.
  22.     rpt.Print strMessage
  23.  
  24.  
Oct 31 '06 #10

P: 98
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
Oct 31 '06 #11

P: 98
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
Nov 3 '06 #12

PEB
Expert 100+
P: 1,418
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
Nov 4 '06 #13

P: 98
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"?
Nov 6 '06 #14

NeoPa
Expert Mod 15k+
P: 31,347
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strPrt As String
  2.  
  3. strPrt = "ABCDE" & VbCrLf & "abcde"
  4. Debug.Print strPrt
would print :
ABCDE
abcde
Nov 6 '06 #15

Post your reply

Sign in to post your reply or Sign up for a free account.