CODEWITHSUNDEEP
vbams-access
laifalove
laifalove

Reputation: 3

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

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]

Upvotes: 0

Views: 51

Answers (1)

Applecore
Applecore

Reputation: 4099

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,

Upvotes: 1

Related Questions