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