pass a SQL to a subform (1 Viewer)

Hi, I built a lstTutor in a form and would like to list the selected tutor with its appointment history in sbfApptHistory (subform). I have no idea of creating fields for it with VBA codes and just created a blank subform.

tblTutor = tutor's table for peronsal particulars
tblTAppt = tutor's appointment table
tblTRank = tutor's staff rank

The tutors are added to lstTutor with a condition from fraOption.

I used a SQL below and Me.sbfApptHistory.Form.RecordSource = SQL

SQL = "SELECT tblTAppt.TRef, tblTAppt.UID, tblTAppt.ApptStart_Dt, tblTAppt.ApptStart_Dt, tblTAppt.ApptEnd_Dt, tblTAppt.AppointedBy, tblTAppt.JobPosition, tblTRank.HDesc " & _
"FROM (tblTAppt INNER JOIN tblTutor ON tblTAppt.TRef = tblTutor.TRef) INNER JOIN tblTRank ON tblTAppt.RankCode = tblTRank.ID " & _
"WHERE tblTAppt.TRef = " & lstTutor.Column(0) & " " & _
"ORDER BY tblTAppt.ApptEnd_Dt DESC"

However, I run into an error of "2467", "the expression you entered refers to an object that is closed or doesn't exist".

I don't know why. Welcome any idea on this.

Here's a link with info on form, subform and subform control that may be helpful.

Good luck.

just created a blank subform

Click to expand...

You cannot just create a blank subform control without a source object. You need to build the form or query first. If you want this in datasheet view simply build the query definition and save the query. Use the query as the source object of the subform. Get rid of the where clause

Now link the subform to the listbox using the link criteria
Master Link Fields: [lstTutor]
Child Link Fields: [TRef]

Now when you make a selection in the listbox the subform will automatically filter.

Thank you for the suggestions.

I managed to work out the pass of a SQL to a subform with the help of real examples from (Advanced Programming In Access 2013) Filtering Sub-Forms Using VBA https://www.youtube.com/watch?v=M1J1rHXR4JY&t=116s.

Thank you again.