Nested SQL string to populate listbox

[colossalUW]

Hi all,

On my form frmParView, I want to view the parents' last and first names in a listbox for the student shown at the top of the form.

Since there can be multiple parents for each student, and multiple students for each parent, my tblStudents and tblParents are linked by tblLinks. Table tblLinks contains only the primary keys from the other tables, StuID and ParID.

My problem is that my listbox will not populate with only the parents for the selected student. I get the error "Microsoft Access can't find the field '|' referred to in your expression" and then it populates with all of the records from tblParents.

Testing the strSQL statment in a query yields an error if there are more than one parent for the student, but work fine if there is exactly one parent for the student. Shouldn't this SQL statement work?

Thanks for any help.

Private Sub Form_Load()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strStuID As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryParView")
strStuID = Me![StuID]

'Turn off screen updating
DoCmd.Echo False


'Build SQL statement
strSQL = "SELECT ParID, ParLast, ParFirst FROM tblParents WHERE ParID = (SELECT ParID FROM tblLinks WHERE tblLinks.StuID ='" & strStuID & "');"

qdf.SQL = strSQL

'Set list box data to equal retrieved SQL statement
[lstSearch].RowSource = ""
[lstSearch].RowSource = strSQL

End Sub

 

[PHV]

strSQL = "SELECT P.ParID, P.ParLast, P.ParFirst FROM tblParents P INNER JOIN tblLinks L ON P.ParID = L.ParID WHERE L.StuID='" & strStuID & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[colossalUW]

Thanks for the reply, PHV.

I still get the error message: "Microsoft Access can't find the field '|' referred to in your expression".

Any thoughts?