Reputation: 3
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
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