Find a value in a Previous Report Line

[PatD]

Microsoft document KB/101081 shows how to do this but I can't get it to work. Neither could another member as discussed on thread 27545 (which is no longer accessible, except by internet search).
As my query generated report is fed from three tables I need to use the proposed CODE method outlined in the MS document; using the DLookup method in a query requires a table to be specified.
The CODE method also shows a default value which I need as an initial value for the Reports first row of data. Subsequent rows are then calculated from the previous row - but that's overcomplicating the issue at this initial stage.
Could anyone start me off please?
Pat

 

[Insane_ai]

Can you post the code you are using?

 

[PatD]

The attached word document is the Microsoft KB101081 document.

My equivalent, held in a Module, is:
Function PrevRecVal(Q As Query, KeyName As String, KeyValue, FieldNameToGet As Long)
'KeyName = The Name of the Queries key value
'KeyValue = The current records key value
Dim rs As Recordset
On Error GoTo Err_PrevRecVal
PrevRecVal = 90 'My required initial default value
Set rs = Q.RecordsetClone 'Get the Query1 recordset
rs.FindFirst "[" & KeyName & "] = " & KeyValue 'Find the current record
rs.MovePrevious 'Move to previous record"
PrevRecVal = rs(FieldNameToGet) 'Return the Result
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

In the Control Source Property for the Report field that requires this data I entered:
=PrevRecVal(Query1, “Skipper_ID”, [Skipper_ID], [TxtEnd])
but Access simply deletes this entry

=DLookUp(“[TxtEnd]”,”Query1”,”[ID]=Reports![RptSkipperRaceResults]![ID]-1”) is accepted but produces #Error on every line of the appropriate report column. Also I assume I cannot introduce a default value with this method. This field is one of three created by math formulaes.
Any Suggestions
Pat

 

[vbaInet]

1. Drop a textbox in the Detail section of the report and set its Control Source to =1 and set the Visible property to No
2. Set its Running Sum property (which is under the Data tab) to Over All
3. Use this code in the PrevRecVal textbox:

=DLookup("[TxtEnd]", "Query1", "[ID] < " & [ID])

 

[PatD]

Many thanks for the response but unfortunately the proposed DlookUp didn’t work for me. The main index for the Query is Skipper_ID which I used after using the plain ID didn’t work. I had #Error on each row. I then tried the Function CalcTxtStart() but this didn’t work per Note 3 below.
The new textbox (with control source set to =1 or 1) I named TxtNew but what does this do please as it doesn’t appear to be referenced anywhere?
Thanks again
Pat



NOTE 1 – There are three calculated fields:

1. TxtRankage calculates OK & produces correct results for each row of the Report
2. TxtEnd compares TxtStart with TxtRankage to produce TxtEnd
3. TxtStart needs to look back at previous row to get TxtEnd

NOTE 2 - Function CalcTxtStart tried as the proposed DLookup did not work
NOTE 3 - In Function CalcTxtStart the last [Skipper_ID] has Err “External Name not Defined”

Function CalcTxtEnd()
If TxtRankage < TxtStart Then
TxtEnd = [TxtStart] - (([TxtStart] - [TxtRankage]) / 10)
ElseIf TxtRankage > TxtStart Then
TxtEnd = [TxtStart] + (([TxtStart] - [TxtRankage]) / 10)
ElseIf TxtEnd = TxtStart Then
TxtEnd = [TxtStart]
End If
End Function

Function CalcTxtStart()
TxtStart = DLookup("[TxtEnd]", "Query1", "[Skipper_ID]<" & [Skipper_ID])
If TxtStart = "" Then
TxtStart = 90 ‘This was added to cover the initial case where there is no TxtEnd
End If
End Function

 

[vbaInet]

I've now incorporated the txtNew textbox:

=IIF([COLOR=Navy][B]txtNew[/B][/COLOR] = 1, [COLOR=Red][B]Put Default Value here[/B][/COLOR], DLookup("[COLOR=Red][B][TxtEnd][/B][/COLOR]", "Query1", "[SkipperID] < " & [SkipperID]))

I suspect txtEnd is not the name of the field, but the name of the textbox the field is linked to. Put the name of the field in there.

What is the data type of SkipperID?

 

[PatD]

I see sail the blind man!! - TxtEnd is the name of a calculated text box in the Report & has no associated field - All down to the requirement not to store excess data. I was using the Function CalcTxtEnd() to provide the data for each report row. Obviously it can't back calculate to the previous row. Using breakpoints in this Function shows all fields to be 'Empty'. The initial value of 90 displays OK in the first line of the Report.
Shipper_ID is a Long Integer, indexed with no duplicates and linked from the main Skipper table to the related Rating Table. The Rating table produces the Query1 report.
I guess I won't be able to produce the report this way.
Pat

 

[vbaInet]

Don't worry, it can still be done. Make txtStart unbound and put something like this in the Format event of the Detail section:

If Me.txtNew = 1 Then
     Me.txtStart = 90
Else
     Me.txtStart = Me.txtStart.Tag
End If

Me.txtStart.Tag = Me.txtEnd

 

[PatD]

Thanks for response.
I created the code as proposed and the opening line reads: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

The "Me.txtStart.Tag = Me.txtEnd" - Returns: Run-time error '94' Invalid use of Null

On the Locals - Cancel reads 0 and FormatCount reads 1.
I tried changing the Sub to Public but the report runs with no data listed for TxtStart or TxtEnd.
Pat

 

[vbaInet]

In that Tab Order of the detail section, ensure that txtEnd comes before txtStart

Replace this line too:

Me.txtStart.Tag = Nz(Me.txtEnd, vbNullString)

 

[PatD]

Nearly there,
Have checked tab orders & all OK and then added a line just prior to the End Sub which reads:
TxtEnd = TxtRankage + TxtStart (this is not the final calculation coding, but a simple test to check the result) TxtRankage calculates and displays in the Report OK. The Report then displays in the following columns:

TxtRankage
Row 1 99.71
Row 2 99.58
Row 3 99.09
TxtStart
Row 1 90
Row 2 189.7199.09
Row 3 189.071
Txt End
Row 1 189.71
Row 2 189.7199.0999.
Row 3 189.7199.09

Pat

 

[vbaInet]

Let's have a look at your db.

 

[PatD]

Compressed Db attached a requested
Pat

 

[vbaInet]

The sequence of events was in the wrong order and there was something wrong with your data too. The calculation you performed for the range was returning Null for one of the records.

See attached.

 

[PatD]

Many many thanks for all your effort and time you put into my problem.
Everything is working great now.
Pat

 

[vbaInet]

No problemo Pat.