Search function issue using Textbox and Listbox

C

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
 
K

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.
 
C

CHJ944

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




Danny Seager said:
you're missing spaces before the where and from sections of your SQL


CHJ944 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top