1

I am a newbie at Microsoft Access and have been trying to wrap my head around an issue.
I am using a form to search for an application number, based on a table (UpdatedFiles).

I am trying to get the form to display the latest entered status on a given application number. However, when I attempt to do so, the result always pulls up the earliest entered status on a given application number.

My form has code which updates the entry date (ENTRYDT) once the record is saved.

I am basically attempting to use the latest ENTRYDT when I search up the application number (APPID). APPID is the table's primary key.

Here's what my code looks like:

Private Sub SearchCommand_Click()
Dim strfilapp As String
Dim strcheck As Variant


strfilapp = "[APPID] = " & "'" & Me!APPID & "'"

strcheck = DLookup("[APPID]", "UpdatedFiles", strfilapp)

If Not IsNull(strcheck) Then
    On Error Resume Next
    [APPID] = DLookup("[APPID]", "UpdatedFiles", strfilapp)
    [LN] = DLookup("[LN]", "UpdatedFiles", strfilapp)
    [FN] = DLookup("[FN]", "UpdatedFiles", strfilapp)
    [PAPERAPP] = DLookup("[PAPERAPP]", "UpdatedFiles", strfilapp & "[ENTRYDT]" >= # LATEST #)
    On Error GoTo 0
Else
    Me.APPID = ""
    MsgBox ("No file with an hyperlinked paper application found for your search. Searching for a file without...")

    strcheck = DLookup("[APPID]", "InitialFiles", strfilapp)

    If Not IsNull(strcheck) Then
        On Error Resume Next
    [APPID] = DLookup("[APPID]", "InitialFiles", strfilapp)
    [LN] = DLookup("[LN]", "InitialFiles", strfilapp)
    [FN] = DLookup("[FN]", "InitialFiles", strfilapp)
        On Error GoTo 0
    Else
        Me.APPID = ""
        MsgBox ("No file found for your search. Try again.")
        Me.SearchField.SetFocus
    End If
End If

End Sub

When I perform the APPID search, I keep getting the application that was first entered into the database, versus the most current (based on ENTRYDT).

I've tried adding the ENTRYDT to the [PAPERAPP] line, as a parameter in DLookup, but just can't seem to figure out how to do it. May you please help me?

Thank you!

AccessNovice
  • 13
  • 3
  • If APPID is a number type field, don't use apostrophe delimiters. Why would you do DLookup for APPID when you already have APPID in Me!APPID? `If Not IsNull(Me.APPID) Then`. Then why set APPID field to APPID? I don't see use of ENTRYDT. Edit question to show your attempt. Code appears to be duplicating name info in multiple records - why? – June7 Feb 22 '21 at 20:20
  • For some reason, the APPID is stored as Short Text... The APPID DLookup is because I use two different tables for this IF ELSE. If the APPID is not found in the UpdatedFiles table, it looks at the raw table for it and pulls all data from the raw table. Raw table is updated live and the updatedfiles table is to snapshot that raw table in a single point in time. – AccessNovice Feb 22 '21 at 21:29
  • I am lost. Both DLookup reference same table. Which table is form bound to? Raw table (name?) is not cumulative data? Records are added/updated/replaced? Those # need to be literal text within quote marks. Assuming ENTRYDT is a date/time field, should not need # for the field, only the criteria parameter. – June7 Feb 22 '21 at 21:49
  • Form is bound to UpdatedFiles table. InitialFiles is cumulative data resulting from a ran query. The UpdatedFiles table has a Hyperlink field and the InitialFiles field does not. We hyperlink the PAPERAPP to the initial file, which in turn makes it an UpdatedFile. I want records to only be updated, not added. However, I have not figured out how to do so, since the table keeps generating new records, even if the APPID is already in UpdatedFiles. I also updated the original post with the full VBA to better demonstrate how the table searching works. – AccessNovice Feb 22 '21 at 22:00
  • Octothorpes and >= operator within quote marks, also need AND: `& " AND [ENTRYDT] >= #" & [LATEST] & "#")` – June7 Feb 22 '21 at 22:05
  • Got it. This is what my rows in the UpdatedFiles table [looks like](http://img4.imagetitan.com/img.php?image=23_capture820.png). When I execute the search with the above code added, I get the first thing entered into the table with the APPID. I want the search to display the PAPERAPP data for the most current ENTRYDT for that APPID. – AccessNovice Feb 22 '21 at 22:45

1 Answers1

1

If you want PAPERAPP associated with most recent - as in Max() - date for given APPID, that probably needs another domain aggregate function.

[PAPERAPP] = DLookup("[PAPERAPP]", "UpdatedFiles", strfilapp & " AND [ENTRYDT] = #" & _
            DMax("ENTRYDT", "UpdatedFiles", "APPID='" & [APPID] & "'") & "#")
June7
  • 19,874
  • 8
  • 24
  • 34
  • Thank you! I tried doing DMax before as well, but nesting the DMax into the DLookup wasn't tried. Worked like a charm. – AccessNovice Feb 22 '21 at 23:46
  • Glad it worked. Pulling the date value to a variable and concatenating variable should work as well. If answer resolves issue, please mark accepted. Upvotes are nice as well. – June7 Feb 22 '21 at 23:47
  • I went ahead and did both. It says it won't publicly display my upvotes since I am under 15 reputation. – AccessNovice Feb 22 '21 at 23:55