Application-defined or object-defined error

[bifteki via AccessMonster.com]

I have a form (frm_s_person_by_surname) which contains a
label in which results from a search in the persons in our database are
displayed. This search is performed through a pop-up form
(frm_s_person_advanced) which contains 6 textboxes and you can search for up
to 6 strings within the person names. When you have entered the strings you
press a button, Search, on the pop-up form.

On the following line:
Forms!frm_s_person_by_surname.sbfrm_s_persons_by_surname.Form.RecordSource
= rs_str
I get a run-time error '2465':
Application-defined or object-defined error

Also, this occurs on the line:
Forms!frm_s_person_by_surname.sbfrm_s_persons_by_surname.sb_display_list.
Visible = False
(the case where all text boxes are null)


I've pasted the code below:

Private Sub btn_find_person_advanced_Click()

Dim pers_sur As String
Dim i As Integer
Dim rs_str As String
Dim txt_str As String

Forms!frm_s_person_by_surname.lbl_results.Visible = True

If (IsNull(txt_person_name_1.Value) And IsNull(txt_person_name_2.Value) And
IsNull(txt_person_name_3.Value) And IsNull(txt_person_name_4.Value) And
IsNull(txt_person_name_5.Value) And IsNull(txt_person_name_6.Value)) Then
Forms!frm_s_person_by_surname.lbl_results.Visible = False
Forms!frm_s_person_by_surname.sbfrm_s_persons_by_surname.sb_display_list.
Visible = False
Exit Sub
Else
Forms!frm_s_person_by_surname.lbl_results.Visible = True

rs_str = "SELECT DISTINCT" & _
"dbo.tbl_Persons.fld_person_id , dbo.tbl_Persons.
fld_person_name, dbo.tbl_Persons.fld_person_surname, dbo.tbl_Companies.
fld_company_name, " & _
"dbo.tbl_Companies.fld_company_id " & _
"FROM dbo.tbl_Companies INNER JOIN " & _
"dbo.itbl_company_person ON dbo.tbl_Companies.
fld_company_id = dbo.itbl_company_person.fld_company_id RIGHT OUTER JOIN " &
_
"dbo.tbl_Persons ON dbo.itbl_company_person.
fld_person_id = dbo.tbl_Persons.fld_person_id" & _
"WHERE (dbo.tbl_Persons.fld_person_surname LIKE '%" &
txt_person_name_1 & "% " & _
"OR (dbo.tbl_Persons.fld_person_name LIKE '%" &
txt_person_name_1 & "% "


If Not IsNull(txt_person_name_2) Then
rs_str = rs_str + "OR (dbo.tbl_Persons.fld_person_surname LIKE '%" &
txt_person_name_2 & "%" & _
"OR (dbo.tbl_Persons.fld_person_name LIKE '%" &
txt_person_name_2 & "%"
End If

End If

Forms!frm_s_person_advanced.SetFocus
DoCmd.Close

Forms!frm_s_person_by_surname.SetFocus
Forms!frm_s_person_by_surname.sbfrm_s_persons_by_surname.Form.RecordSource
= rs_str

sb_display_list.Visible = True

End Sub

 

[bifteki via AccessMonster.com]

Seems like I solved it. The subform control in which the subform is, is
sb_display list, so I've replaced sbfrm_s_persons_by_surname with it (it may
seem weird that I don't exactly know the objects and controls of the project,
but the project is not mine, I'm just adding more functionallity to it). Now,
however, I get an error "Incorrect syntax near keyword 'LIKE'". I think it's
pretty clear what this error refers to but I just can't find a mistake in the
SELECT string. Can anyone help with this?

PS: I noticed that I don't close the single quotation marks in which the
string for the name is enclosed. eg '%ol