Retrieving most recent value in Microsoft Access form based on another value

Question

I have a form built for the team to log calls made to customers. They may have multiple calls with the same customer who all have a unique ID assigned. I've built a form so that the team member can select a customer ID in the combo box and then using VBA, unbound text boxes will display the customer's name, phone number, and address (which is stored on a customer demographics table). All of this part so far has been working just fine.

My problem is that there is a text box on my form where team members can insert notes about the call. I've been asked by the team lead to make it so that when the customerID is selected, that notes box automatically populates with the last call's notes, and that team members can enter add on more notes. So for example in the last call on 4/1, the notes were "4/1 - Spoke to customer, order is on the way", and now it's 4/8, they're calling customer and want to see the last notes, the box should show that note from 4/1. Is there a way to do this? I've tried using the combo box option tying this to the customer ID combo box but can't quite get it to work. Thank you!

SELECT Notes,
   CustID FROM (SELECT Call1.CustID,
   Count(*) AS Rank,
   Call1.DateCalled, Call1.Notes FROM Call AS Call1 
INNER JOIN Call AS Call2 ON Call1.CustID = Call2.CustID 
WHERE ((Call2.DateCalled>=Call1.DateCalled)) 
GROUP BY Call1.CustID, Call1.DateCalled ORDER BY 1, 2)  WHERE Rank=1)  AS Call ON CustTable.CustID=Call.[CustID]

Of course there's a way to do this - probably more than one. Your question is too broad. Make an attempt at coding and when you have specific issue, post question with code for analysis. What does "can't quite get it to work" mean - error message, wrong result, nothing happens? — June7
Possible approaches include: 1) domain aggregate functions in VBA; 2) recordset in VBA; 3) memo type field with AppendOnly property set to Yes. — June7
So I've tried using the combo box, where in the row source data, the query will join the call table to itself, and then sort based on call date. The result is that either nothing appears in the notes box or a weird non-English character appears, neither of which are actually in the notes for that call. I've included the code I've used so far in my edited post. — laifalove

Applecore Applecore · Accepted Answer · 2020-04-23T17:37:40

Below is some VBA code that gets the last set of notes (by using TOP/ORDER BY DateCalled DESC) for the current customer as selected in a combo box where DateCalled doesn't equal the current call date (shown in txtDateCalled):

Private Sub Combo0_AfterUpdate()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim strSQL As String
    Set db = DBEngine(0)(0)
    strSQL = "SELECT TOP 1 Notes " _
        & " FROM Call " _
        & " WHERE CustID=" & Me!Combo0 _
        & " AND DateCalled<>" & Format(txtDateCalled, "\#mm\/dd\/yyyy\#") _
        & " ORDER BY DateCalled DESC;"
    Set rsData = db.OpenRecordset(strSQL)
    If Not (rsData.BOF And rsData.EOF) Then
        Me!txtLastNotes = rsData!Notes
    Else
        Me!txtLastNotes = ""
    End If
sExit:
    On Error Resume Next
    rsData.Close
    Set rsData = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "frmCall!Combo0_AfterUpdate", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

In order to populate the other text boxes, which are based on the Customer table, you may want to look at adding the columns to the combo box, with their ColumnWidth set to 0 (so the user can't see them), and then set these text boxes to use the hidden columns.

Regards,

Retrieving most recent value in Microsoft Access form based on another value

1 Answers