look up field in previous record

Posted on 2006-03-23
Medium Priority
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
  • 2
LVL 58

Accepted Solution

harfang earned 2000 total points
ID: 16276800

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

ID: 16277290

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

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 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