Search function issue using Textbox and Listbox

[CHJ944]

I have created a database with a table of personnel. When someone wants to
edit a person's detail, a dialog box opens so they can search for the
relevant record. The dialog box has an unbound text box [Text8], an unbound
list box [Surnamelist] and a command button [Search1]. The user enters a
surname in the text box, clicks the 'Search' button and I would like the
possible results to display in the listbox.
I have read lots of articles about getting this right but for some reason
(probably very straightforward!) I can't get the results to show in the
listbox.

Any help or advice would be greatly appreciated. Many thanks.


The code for the 'Search' command button is as follows:

Private Sub Search1_Click()
On Error GoTo Err_Search1_Click

If Len(Trim$(Me.Text8 & vbNullString)) = 0 Then
MsgBox "Please enter a Surname.", , "Missing Data"
Me.Text8.SetFocus

ElseIf IsNull(DLookup("[Surname]", "[Personnel]", "[Surname]= '" & [Text8] &
"'")) Then
MsgBox "This Surname does not exist in the database. Please check what
you have entered.", , "No Record Found"
Me.Text8.SetFocus

Else
Me.SurnameList.RowSource = "SELECT Personnel.[IDNo], Personnel.[Title],
Personnel.[Initials], Personnel.[Surname]" & _
"FROM Personnel" & _
"WHERE
(((Personnel.Surname)=[Forms]![EditPerson1]!Text8]));"

End If

Exit_Search1_Click:
Exit Sub

Err_Search1_Click:
MsgBox Err.Description
Resume Exit_Search1_Click

End Sub

 

[Danny Seager]

you're missing spaces before the where and from sections of your SQL

 

[Klatuu]

You are going to a lot of work to make it harder than it needs to be.
An easier way is to use an unbound combo box on the form used just for such
searches. This is what a combo box does better than any other control.

 

[CHJ944]

Many thanks for this. All sorted now. I also needed to add spaces around
the = sign in the SQL. (Just four little spaces....!!)

you're missing spaces before the where and from sections of your SQL

I have created a database with a table of personnel. When someone wants to
edit a person's detail, a dialog box opens so they can search for the
relevant record. The dialog box has an unbound text box [Text8], an unbound
list box [Surnamelist] and a command button [Search1]. The user enters a
surname in the text box, clicks the 'Search' button and I would like the
possible results to display in the listbox.
I have read lots of articles about getting this right but for some reason
(probably very straightforward!) I can't get the results to show in the
listbox.

Any help or advice would be greatly appreciated. Many thanks.


The code for the 'Search' command button is as follows:

Private Sub Search1_Click()
On Error GoTo Err_Search1_Click

If Len(Trim$(Me.Text8 & vbNullString)) = 0 Then
MsgBox "Please enter a Surname.", , "Missing Data"
Me.Text8.SetFocus

ElseIf IsNull(DLookup("[Surname]", "[Personnel]", "[Surname]= '" & [Text8] &
"'")) Then
MsgBox "This Surname does not exist in the database. Please check what
you have entered.", , "No Record Found"
Me.Text8.SetFocus

Else
Me.SurnameList.RowSource = "SELECT Personnel.[IDNo], Personnel.[Title],
Personnel.[Initials], Personnel.[Surname]" & _
"FROM Personnel" & _
"WHERE
(((Personnel.Surname)=[Forms]![EditPerson1]!Text8]));"

End If

Exit_Search1_Click:
Exit Sub

Err_Search1_Click:
MsgBox Err.Description
Resume Exit_Search1_Click

End Sub