Search form using multiple tables and Union

I downloaded and redsigned a search form, which works great if I am looking
at one table. Now I am redesigning another one which uses multiple tables
and a Union. After each key stroke in a textbox it should update my list
box. However, I can't seem to get this thing to work. I have even tried it
without the union to see what could happen, again it doesn't work.
Can someone point out what I am doing wrong here? I would really appreciate
it, thanks.
txtSearchString2 = Me![txtLastName].Text

strSQL = "SELECT * tblPracticalData.UniquePracticalIdentifier AS [Unique
ID], tblTypeOfPractical.PracticalName AS [Type of Practical],
tblTypeOfQualification.TypeOfQualification AS [Type of Qual],
tblStudents.LName AS [Last Name], tblStudents.FName AS [First Name],
tblStudents.STUDENTID"
strSQL = strSQL & "FROM tblTypeOfPractical INNER JOIN
(tblTypeOfQualification INNER JOIN (tblStudents INNER JOIN tblPracticalData
ON tblStudents.StuID = tblPracticalData.StudentOne) ON
tblTypeOfQualification.QUALTYPEID = tblPracticalData.Qual) ON
tblTypeOfPractical.PRACTYPEID = tblPracticalData.Practical"
strSQL = strSQL & "WHERE ((tblStudents.LName) Like '" & txtSearchString2 &
"*')"
strSQL = strSQL & "ORDER BY tblStudents.LName, tblStudents.FName,
tblStudents.StudentID"
strSQL = strSQL & "UNION SELECT * tblPracticalData.UniquePracticalIdentifier
AS [Unique ID], tblTypeOfPractical.PracticalName AS [Type of Practical],
tblTypeOfQualification.TypeOfQualification AS [Type of Qual],
tblStudents.LName AS [Last Name], tblStudents.FName AS [First Name],
tblStudents.STUDENTID"
strSQL = strSQL & "FROM tblTypeOfPractical INNER JOIN
(tblTypeOfQualification INNER JOIN (tblStudents INNER JOIN tblPracticalData
ON tblStudents.StuID = tblPracticalData.StudentTwo) ON
tblTypeOfQualification.QUALTYPEID = tblPracticalData.Qual) ON
tblTypeOfPractical.PRACTYPEID = tblPracticalData.Practical"
strSQL = strSQL & "WHERE ((tblStudents.LName) Like '" & txtSearchString2 &
"*')"
strSQL = strSQL & "ORDER BY tblStudents.LName, tblStudents.FName,
tblStudents.StudentID"

Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtLastName.SetFocus

Ok to get this to work I had come up with this.

strSQL = "SELECT * FROM [qryEvaluationPeople]"
strSQL = strSQL & "WHERE ((qryEvaluationPeople.LName) Like '" &
txtSearchString2 & "*')"
strSQL = strSQL & "UNION SELECT * FROM [qryEvaluationPeople2]"
strSQL = strSQL & "WHERE ((qryEvaluationPeople2.LName) Like '" &
txtSearchString2 & "*')"

However, this method prevents me to set my where clasue to something like
WHERE ((qryEvaluationPeople.Last Name) Like '" & txtSearchString2 & "*')".
If I want to use Column headings such as Last Name for my list box. Any
solutions?



"Fysh" wrote:

I downloaded and redsigned a search form, which works great if I am looking
at one table. Now I am redesigning another one which uses multiple tables
and a Union. After each key stroke in a textbox it should update my list
box. However, I can't seem to get this thing to work. I have even tried it
without the union to see what could happen, again it doesn't work.
Can someone point out what I am doing wrong here? I would really appreciate
it, thanks.
txtSearchString2 = Me![txtLastName].Text

strSQL = "SELECT * tblPracticalData.UniquePracticalIdentifier AS [Unique
ID], tblTypeOfPractical.PracticalName AS [Type of Practical],
tblTypeOfQualification.TypeOfQualification AS [Type of Qual],
tblStudents.LName AS [Last Name], tblStudents.FName AS [First Name],
tblStudents.STUDENTID"
strSQL = strSQL & "FROM tblTypeOfPractical INNER JOIN
(tblTypeOfQualification INNER JOIN (tblStudents INNER JOIN tblPracticalData
ON tblStudents.StuID = tblPracticalData.StudentOne) ON
tblTypeOfQualification.QUALTYPEID = tblPracticalData.Qual) ON
tblTypeOfPractical.PRACTYPEID = tblPracticalData.Practical"
strSQL = strSQL & "WHERE ((tblStudents.LName) Like '" & txtSearchString2 &
"*')"
strSQL = strSQL & "ORDER BY tblStudents.LName, tblStudents.FName,
tblStudents.StudentID"
strSQL = strSQL & "UNION SELECT * tblPracticalData.UniquePracticalIdentifier
AS [Unique ID], tblTypeOfPractical.PracticalName AS [Type of Practical],
tblTypeOfQualification.TypeOfQualification AS [Type of Qual],
tblStudents.LName AS [Last Name], tblStudents.FName AS [First Name],
tblStudents.STUDENTID"
strSQL = strSQL & "FROM tblTypeOfPractical INNER JOIN
(tblTypeOfQualification INNER JOIN (tblStudents INNER JOIN tblPracticalData
ON tblStudents.StuID = tblPracticalData.StudentTwo) ON
tblTypeOfQualification.QUALTYPEID = tblPracticalData.Qual) ON
tblTypeOfPractical.PRACTYPEID = tblPracticalData.Practical"
strSQL = strSQL & "WHERE ((tblStudents.LName) Like '" & txtSearchString2 &
"*')"
strSQL = strSQL & "ORDER BY tblStudents.LName, tblStudents.FName,
tblStudents.StudentID"

Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtLastName.SetFocus