Re: Get Value from Next Record for this record

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Bob (anonymous_at_discussions.microsoft.com)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 14:28:31 -0800

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);

>-----Original Message-----
>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
>
>--
>Duane Hookom
>MS Access MVP
>
>
>"Bob" <anonymous@discussions.microsoft.com> wrote in
message
>news:e6c101c3f04c$c4eaaa80$a501280a@phx.gbl...
>>
>> >-----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
>> >
>> >
>> >"Bob P" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:ddb701c3effb$e882ecb0$a001280a@phx.gbl...
>> >> 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
>> >>
>> >
>> >
>> >.
>> >I want to compare the values in the same field of a
>> report driven by a query - the current field and the
same
>> field in the next record.
>>
>> Bob
>
>
>.
>

Relevant Pages

  • Re: Date Range in 2007
    ... How can I check if IsLoadedfunction is in my database. ... I copied the Report Date Range from Northwind ... Allen Browne - Microsoft MVP. ... Private Sub Report_Open ...
    (microsoft.public.access.reports)
  • Re: Date Range in 2007
    ... It does appear in the Northwind sample database, so may people copy it from there. ... Private Sub Report_Open ... Cancel = True ... DoCmd.Close acForm, "Report Date Range" ...
    (microsoft.public.access.reports)
  • Re: If Statement for Sub-Form
    ... Making sure users cant and have to ... do things is a whole other Database. ... > Private Sub LabelName_Click ... >> If a person tries to Invoice a customer, i.e. open the Report ...
    (microsoft.public.access.forms)
  • Re: Timely: How do I add a date range query for a report?
    ... database that comes with the program. ... Your report can use a table, a saved query, or a SQL statement as its Record Source. ... Private Sub Report_NoData ...
    (microsoft.public.access.reports)
  • Re: "Overflow Error" - need help please.
    ... Okay, so the Overflow Error happens in the form, even when it has NO code. ... Then compact the database: ... INSERT INTO tblContacts (ctEnteredDate, ctPeopleSoftID, ctLastName1, ... Private Sub Form_Current ...
    (microsoft.public.access.modulesdaovba)