MS Access: when working with Continuous Forms, how can I run a query that filters by ID of each record and display result in Textbox?

Here is my scenario:

I have a database for projects I manage. In this database, I have a table for Projects (tblProjects) and a table for Hot Notes (tblHotNotes) that look like this:

tblProjects:

• ProjectID
• ProjectName

tblHotNotes:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

• HotNoteID
• ProjectID (fk)
• HotNote
• HotNoteDate

I also have a continuous form called frmProjectList that lists all projects and displays their info. In this form, I display the ProjectID in a Textbox called txtProjectID and I have another Textbox called HotNotes_Textbox where I want to show the CONCAT_SQL function return.

I found and adapted a VBA Function that runs a query, formats the results, and returns it as a string:

Public Function CONCAT_SQL(strSQL As String) As String

   Dim r As ADODB.Recordset
   Dim a1 As Variant
   Set l = CreateObject("System.Collections.ArrayList")
   Dim s As String
   Dim outer As Long

   Set r = New ADODB.Recordset
   r.Open strSQL, CurrentProject.Connection, 1

   a1 = r.GetRows()
       
   For outer = LBound(a1, 2) To UBound(a1, 2)
        s = s + CStr("<b>" & a1(2, outer) & "</b>: " & a1(1, outer) & "<br>")
   Next

   CONCAT_SQL = s

End Function

When the frmProjectList loads, the Sub below is called and the CONCAT_SQL function is called within it. The SQL query is defined here:

Private Sub Form_Load()

    Me.HotNotes_Textbox = CONCAT_SQL("SELECT [ProjectName], tblHotNotes.HotNote, tblHotNotes.HotNoteDate FROM tblProjects INNER JOIN tblHotNotes ON tblProjects.ProjectID = tblHotNotes.ProjectID WHERE tblHotNotes.ProjectID = " & Me.txtProjectID)
     
End Sub

Problem

When I Open frmProjectList it shows all records as expected but the HotNotes_Textbox Textbox shows in all records only the results of the query for the first record. i.e. tblHotNotes.ProjectID = 1 as opposed to having tblHotNotes.ProjectID changing for each record.

Is there a way to fix this and have different results showing in the HotNotes_Texbox?

Thank you!

>Solution :

Call function from textbox ControlSource, don’t set textbox value with VBA.

I doubt you need to include tblProjects in the SQL.

=CONCAT_SQL("SELECT HotNote, HotNoteDate FROM tblHotNotes WHERE ProjectID = " & [txtProjectID])