buzzcarter
asked on
look up field in previous record
I have a Form where the user can scoll thought records and edit them. As a new record is current I need to display in a textbox the some previous record fields that matches criteria from the current record.
I am trying to use DLast but it is not consistent. The first record seems to display the information correctly and as the user scolls the previous record information matches the criteria but skips a record or two that is more current.
'LastFlightRec is a Query
Public LastFlightLog
Public OldPFB
Public OldGOB
Private Sub Form_Current()
'This is the line that returns the incorrect record. Although the criteria is correct.
LastFlightLog = DLast("[ID]", "LastFlightRec", "[Nnumber] = [TailNumber] AND [ID]< " & Me.IDest)
If IsNull(LastFlightLog) Then Exit Sub
Me.LastFL = LastFlightLog
OldPFB = DLast("[PriceOfFOB]", "LastFlightRec", "[ID]= " & LastFlightLog)
Me.LastFuel = OldPFB
OldGOB = DLast("[GalsOnB]", "LastFlightRec", "[ID]= " & LastFlightLog)
If Me.GndFuel > 0 Then OldGOB = Me.GndFuel / 6.75
Me.LastGals = OldGOB
Me.LastLbs = Round(OldGOB * 6.75, 0)
End Sub
This is the results: The current displayed record [ID] = 480; Previous Record is displayed [ID] =474; But there is a record in the Query with [ID]=478 that matches the criteria. The Query is sorted on [ID];Desc
Am I giving to much information? Just trying to give you the picture.
I am trying to use DLast but it is not consistent. The first record seems to display the information correctly and as the user scolls the previous record information matches the criteria but skips a record or two that is more current.
'LastFlightRec is a Query
Public LastFlightLog
Public OldPFB
Public OldGOB
Private Sub Form_Current()
'This is the line that returns the incorrect record. Although the criteria is correct.
LastFlightLog = DLast("[ID]", "LastFlightRec", "[Nnumber] = [TailNumber] AND [ID]< " & Me.IDest)
If IsNull(LastFlightLog) Then Exit Sub
Me.LastFL = LastFlightLog
OldPFB = DLast("[PriceOfFOB]", "LastFlightRec", "[ID]= " & LastFlightLog)
Me.LastFuel = OldPFB
OldGOB = DLast("[GalsOnB]", "LastFlightRec", "[ID]= " & LastFlightLog)
If Me.GndFuel > 0 Then OldGOB = Me.GndFuel / 6.75
Me.LastGals = OldGOB
Me.LastLbs = Round(OldGOB * 6.75, 0)
End Sub
This is the results: The current displayed record [ID] = 480; Previous Record is displayed [ID] =474; But there is a record in the Query with [ID]=478 that matches the criteria. The Query is sorted on [ID];Desc
Am I giving to much information? Just trying to give you the picture.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Thanks for the compliment, glad it worked!
Good luck with your project.
(°v°)
Good luck with your project.
(°v°)
ASKER
With your input my code now is
LastFlightLog = DMax("[ID]", "LastFlightRec", "[ID]< " & Me.IDest)
If IsNull(LastFlightLog) Then
Me.LastFL = Null
Me.LastFuel = Null
Me.LastGals = Null
Me.LastLbs = Null
Else
Me.LastFL = LastFlightLog
OldPFB = DLookup("[PriceOfFOB]", "LastFlightRec", "[ID]like " & LastFlightLog)
Me.LastFuel = OldPFB
OldGOB = DLookup("[GalsOnB]", "LastFlightRec", "[ID]like " & LastFlightLog)
If Me.GndFuel > 0 Then OldGOB = Me.GndFuel / 6.75
Me.LastGals = OldGOB
Me.LastLbs = Round(OldGOB * 6.75, 0)
End If
I'll search the remaining code and recode everything that included DLast.
Thank you for the quick answer and the very helpful suggestions. You make this look easy.
Buzz