Get Value from Next Record for this record

  • Thread starter Thread starter Bob P
  • Start date Start date
B

Bob P

I want to color the background in the current text box
based on the next records value for this text box. How do
I get that value? I know how to handle the background.

Thanks

Bob
 
This might be doable in a query. I doubt you can perform this in a report.
Since we know nothing about your data, we won't be of much help.
 
-----Original Message-----
This might be doable in a query. I doubt you can perform this in a report.
Since we know nothing about your data, we won't be of much help.

--
Duane Hookom
MS Access MVP





.
I want to compare the values in the same field of a
Click to expand...
report driven by a query - the current field and the same
field in the next record.

Bob
 
My previous post was a subtle nudge to get you to provide more information.
Consider sharing:
-table and field names
-some sample data
-what you would like to eventually display
 
I found a way that should work but does not. When I step
through it it appears to work in that the values get set
the way I want them. However, when the report appears on
the screen every text15 box is marked the same. If I
replace the lngNote variable with a constant, say 3, then
everywhere the note field {Text13} is 3 the text15 box is
marked correctly.
Here is the documentation:

Detail Event in report:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
'Set background color dependent on whether value in next
record equals value for this record
If lngNote = Me![Text13].Value Then
Me![Text15].BackColor = '12632256
Else
Me![Text15].BackColor = 16777215
End If
'Move the duplicate recordset forward to make the next
value available
If rstQry.AbsolutePosition + 1 <> rstQry.RecordCount
Then
rstQry.MoveNext
lngNote = rstQry![Note].Value 'Note is a
long integer
Else
lngNote = 0 'Set note
so that a match does not occur for the last record
End If
End Sub

On Open Event:

Private Sub Report_Open(Cancel As Integer)
TrayNo = MirrorDB() 'Sets up the duplicate
recordset and positions it one record ahead
TrayNo = 0 'Not used here
PreviousNote = 1 'Not used here
NoteNo = 0 'Not used here
lngNote = 1 'Variable between the
duplicate recordset and the report recordset
End Sub


General Declartions in a module:

Option Compare Database
Option Explicit
Public TrayNo As Double 'Not used here
Public NoteNo As Integer 'Not used here
Public PreviousNote As Integer 'Not used here
Public rstQry As Recordset
Public lngNote As Long
Public dbs As DATABASE

Function:

Function MirrorDB() As Integer

' Return Database variable that points to current
database.
Set dbs = CurrentDb
' Open dynaset-type Recordset object.
Set rstQry = dbs.OpenRecordset("qrySequence")
' Set current record.
rstQry.MoveFirst
' Move to last record.
rstQry.MoveLast
' Move to second record.
rstQry.MoveFirst
rstQry.MoveNext
MirrorDB = 1
End Function

Query that supports the report

SELECT DISTINCTROW [numbered show list].Sequence,
[numbered show list].ROLL, [numbered show list].FRAME, IIf
(Len([tblFilm].[Comment])>0,[tblFilm].[Comment],
[tblAuxFilm].[Comment]) AS Comment, [numbered show
list].NOTE
FROM (([numbered show list] LEFT JOIN tblFilm ON
([numbered show list].FRAME = tblFilm.[Frame Count]) AND
([numbered show list].ROLL = tblFilm.Roll)) LEFT JOIN
tblAuxFilm ON ([numbered show list].FRAME = tblAuxFilm.
[Frame Count]) AND ([numbered show list].ROLL =
tblAuxFilm.Roll)) LEFT JOIN tblNotes ON ([numbered show
list].FRAME = tblNotes.Frame) AND ([numbered show
list].ROLL = tblNotes.Roll);
 
Back
Top