View only most recently added attachment in Access report

Question

I'm using an access database as a member management application for our swimming pool. I use a report to print out all of the season passes that have a picture ID. The report uses a query to only print passes for patrons who have had their photo taken.

SELECT tblPassHolders.[PASS HOLDER NAME], tblPassHolders.PHOTO.FileData, tblPassHolders.BARCODE, tblPassHolders.[FAMILY PASS], tblFamilyPass.Expires
FROM tblFamilyPass INNER JOIN tblPassHolders ON tblFamilyPass.ID = tblPassHolders.FamilyID
WHERE (((tblPassHolders.PHOTO.FileData) Is Not Null) AND ((tblFamilyPass.Expires)>Now()) AND ((tblPassHolders.Printed)=False));

This is year two of using the application, and the problem I'm having is when people re-take their pictures for their pass, then my report prints out a pass for each photo attachment attached to their record.

I'm very amateur with Access still and I'm struggling to figure out how to edit my report/query so that the report will only print one pass for each patron using the most recent photo attached to their record. One solution is to simply delete the old photos so there's only ever one attachment, but I'd like to figure out how to make it work even if there are multiple attachments. I've been playing around with DLast() but I'm pretty sure I'm doing it wrong.

How do I make a query that will only show the most recently added attachment?

please post the query you are using to help. likely you should have a filter (`WHERE`) to only take the most recent photo — serakfalcon, May 16 '18 at 17:19
Why keep multiple photos? Why not replace the photo? Try a TOP 1 subquery. Review http://allenbrowne.com/subquery-01.html#TopN — June7, May 16 '18 at 17:53
Its hard to figure out your data and query with the Design Mode image you posted. We are mostly talking SQL here. So, in the Query designer, hit the `SQL View` button top left, copy the SQL code and paste it in your question. — Thomas G, May 16 '18 at 17:53
How do you identify which of the two `PHOTO` records is the newer one? — SunKnight0, May 16 '18 at 18:14
If you are keeping multiple photos in 1 attachment field, this is a multi-value type field. Requires query to expand the multiple values to individual records. Suggest you read up on use of multi-value field in query. Simplest solution is to not save multiple photos, replace photo. — June7, May 16 '18 at 19:05
Saving objects in table can quickly use up Access 2GB size limit. Maybe your images are small enough files this may never happen, but you should be aware of possibility. Another approach is to save images in folder and link to those files with Image control's ControlSource property. Review https://stackoverflow.com/questions/50207997/access-form-abc-picture-xyz-jpg-makes-listbox-textboxes-blink-once/50269085#50269085 — June7, May 16 '18 at 19:10
@ThomasG Discovering that there is an `SQL View` just changed my life for the better. — Lei-Lonnie, May 16 '18 at 19:22
@SunKnight0 My question exactly. If the answer to that question is, "you can't" then I guess my above question is moot. I was assuming that access perhaps kept track of which attachment was added when. — Lei-Lonnie, May 16 '18 at 19:25
You don't have some sort of auto-increment or _creation_date_ field in `tblPassHolders` ? How can we then pickup the last one ? — Thomas G, May 16 '18 at 19:31
@ShemSeger The answer to your question is "it can if designed properly". If your photo table has an `auto-increment primary key ID field` as most table should always have unless there is a compelling reason not to, then your answer is there, just grab the record with the higher ID. — SunKnight0, May 16 '18 at 19:34
@SunKnight0, but there isn't a Photo table, it's an attachment field with multiple embedded images. See my previous comments. And now I see the query is expanding the PHOTO field to multiple records. So name the photos with a sequential identifier, and selecting the TOP 1 with the FileName property may be possible. — June7, May 16 '18 at 19:52
@June7 right, never used this as I generally try to keep the option to migrate to a better back end open. looking into it, you are right, the filename is the only possible criteria so unless it is a date based one or there is some trusted naming consistency, there is no way. — SunKnight0, May 16 '18 at 19:58
@ThomasG There is, but that doesn't help with knowing which attachment in the attachment field in `tblPassHolders` came first. — Lei-Lonnie, May 16 '18 at 22:26
This is a great question and I was interested in the internal structure of attachments to see if the sorting could be tweaked. Unfortunately it looks like your stuck with the "sort by filename". Do all your uploaded files come from one folder or from everywhere? There might be a way of cleaning up files based on the dates - otherwise I think you should implement an "Attachment Clear" routine when a new file is uploaded - good example of clearing `child recordsets` at https://stackoverflow.com/questions/26691174/display-attachment-for-specific-record-microsoft-access — dbmitch, May 17 '18 at 00:59
Going forward you could always modify the upload code to change the current filename to something like "000000-latestpic". That will bring it to the top of the list and subsequent adds will ask you if you want to replace that file — dbmitch, May 17 '18 at 01:04
@dbmitch That's clever. Don't suppose you care to compose an answer demonstrating what modifications to the upload code would look like? And what the query would look like? It would make a very up-votable answer, and sounds like the best option thus far. — Lei-Lonnie, May 17 '18 at 03:07
How do you add your new photos now? In a form that edits the record - or do you just double click the table's field. It's easier if I have starting form and click event to work with. Otherwise I could create a subset of the form just for editing an existing record to add a new photo. — dbmitch, May 17 '18 at 03:43
@dbmitch Ya, I've just been using the native file selector in the form; double clicking the field, but adding a button is no problem. — Lei-Lonnie, May 17 '18 at 14:38

score 1 · Accepted Answer · edited May 17 '18 at 16:33

Here is an example of what I was suggesting you could do instead of finding the most recently added attachment.

As discussed it's impossible unless you implement some kind of naming convention that pops your latest file to the top, because Microsoft shows the attachment field in order of filename.

Here's an example that you can use a button to control inserting the attachments. It's based on the Stack Overflow example

Once you finish testing you can add Cancel=True to the Photo_DblClick event to completely control your Photo Attachments field

Private Sub cmdAddNewPhoto_Click()

    Dim rsPhotos        As DAO.Recordset2
    Dim rsParent        As DAO.Recordset2

    Dim strImagePath    As String

    If MsgBox("Add New Photo?", vbQuestion + vbOKCancel, "Add Photo?") = vbOK Then

        ' Get New Photo
        ' Note that you need to add a reference to Microsoft Office ##.0 Object Library
        ' using Tools | References... from the VBA interface for the file picker to work
        With Application.FileDialog(msoFileDialogFilePicker)
            ' Prevent multiple selections
            .AllowMultiSelect = False
            ' Set the caption of the dialog box
            .Title = "Please select a photo"
            ' Add filters for common image formats
            .Filters.Clear
            .Filters.Add "JPG Files (JPG)", "*.JPG"
            .Filters.Add "JPEG Files (JPEG)", "*.JPEG"
            .Filters.Add "PNG Files", "*.PNG"
            .Filters.Add "Bitmap Files", "*.BMP"

            If .Show = True Then ' File selected
                strImagePath = .SelectedItems.item(1)
            End If
        End With

        If strImagePath <> "" Then

            ' First clear all old photos if desired
            If Photo.AttachmentCount > 0 Then
                If MsgBox("Clear Previous Photo(s)?", vbQuestion + vbOKCancel, "Remove All Photos?") = vbOK Then
                    ' Clear previous attachments
                    ' (we only want one attachment at a time)
                    Set rsPhotos = Me.Recordset.Fields("Photo").Value
                    With rsPhotos
                        Do While Not .EOF
                            .Delete
                            .MoveNext
                        Loop
                        .Close
                    End With
                    ' Clear last displayed photo
                    Photo.Requery
                End If
            End If

            ' Put parent record in edit mode
            Set rsParent = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
            With rsParent
                ' Get Cureent Matching Record using Primary Key
                .FindFirst "BarCode = " & Me!barcode
                .Edit
                DoEvents
            End With

            ' Next Add the attachment selected by the user
            Set rsPhotos = rsParent.Fields("Photo").Value
            With rsPhotos
                .AddNew
                .Fields("FileData").LoadFromFile strImagePath
                If Photo.AttachmentCount > 0 Then
                    ' Rename so it pops up to first file - and keep extension
                    .Fields("Filename").Value = "00000LatestPic" & Mid$(strImagePath, InStrRev(strImagePath, "."))
                End If
                .Update
                .Close
            End With

            ' Update the parent record
            With rsParent
                .Update
                .Close
            End With

            Set rsPhotos = Nothing
            Set rsParent = Nothing

            ' Refresh Photo Display
            Photo.Requery
        End If
    End If
End Sub

View only most recently added attachment in Access report

1 Answers1