look up field in previous record

Posted on 2006-03-23
Last Modified: 2008-02-26
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.
Question by:buzzcarter
    LVL 58

    Accepted Solution


    You are right, DLast is not consistent. It will mangle the sort order of your query when adding the WHERE clause. You will need your own custom function to find a "last" record using a specific sort order (tell me if you need it)

    However, from the look of it, you actually want the "highest" ID below the current one. You can get this by using DMax() instead:

        LastFlightLog = DMax("ID", "LastFlightRec", "Nnumber=TailNumber AND ID<" & Me.IDest)

    BTW: the criteria "Nnumber=TailNumber" could be included in the quey if you do not use if for other purposes.

    Ah, and you should also manage the IsNull case, as in:

        If IsNull(LastFlightLog) Then
            Me.LastFL = Null
            ' etc. setting other controls to Null
            Me.LastFL = LastFlightLog
            ' etc. filling the other controls
        End If


    Author Comment


    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
        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.
    LVL 58

    Expert Comment

    Thanks for the compliment, glad it worked!
    Good luck with your project.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now