Listbox search

[j9070749]

I have a listbox (names list15), which lists peoples details. This listbox is based on a query, which basically has all of the information in the table does (can post the sql for this if needed)



I have 4 textboxes (where we will type the criteria to search) called:
Surname
Address
Code 1
Code 2

A search button called search

I want to add code behind the search button which will filter list15 to show the results.
Is this possible or will the reuslts have to be shown in a separate list box?

I am new to programming, so unsure how to go about this. Can anyone give me any tips/examples on how to start/do the code to do this please?

Also behind the search button, if 'after update' I put the code [me.refresh] would the search results update after each key was pressed?

Thanks in advance!

[j9070749]

After searching, I have found some code which works (i know i should have searched first but didn't search on this site!)

The code highlights the correct field in the listbox, but i want it to filter, so that it only shows the fields in the listbox which match the crietiera in 'search' my code so far is:



Private Sub search_AfterUpdate()
Dim db As Database
Dim ssql As String
Dim rst As Recordset
Dim strWhere As String
Set db = CurrentDb
strWhere = "((([Surname]) like '*" & Me.search & "*') OR (([CODE 2]) like '*" & Me.search & "*') OR (([ADDRESS1]) like '*" & Me.search & "*') OR (([CODE 1]) like '*" & Me.search & "*'))"
ssql = "SELECT * FROM [Customer Details] WHERE " & strWhere
Set rst = db.OpenRecordset(ssql)
rst.MoveFirst
List15 = rst.Fields("ID")
rst.Close
db.Close
End Sub

[j9070749]

Also another problem I am coming across is I want a message box to appear when no records are found.
I have tried:


On Error GoTo error_handler

Set rst = db.OpenRecordset(ssql)
rst.MoveFirst
List15 = rst.Fields("ID GP")
rst.Close

error_handler:
MsgBox "No matches found"
This works when no criteria matches, but also happens when it does match. I have tried moving the 'On Error GoTo error_handler' line to the top of the code


I have also tried:

Set rst = db.OpenRecordset(ssql)
If rst.movefirst is false then
msgbox "No matches found, please try again"
Else
rst.MoveFirst
List15 = rst.Fields("ID GP")
end if
rst.Close

But his does not work (once again I am new to VB so it may be a obvious error)

Thanks in advance

[nicknameoscar]

Set rst = db.OpenRecordset(ssql)
If rst.BOF AND rst.EOF then
MsgBox "No matches Found, please try again"
(I am not familiar with the above method of opening a recordset but you might want to add rst.Close here)
Exit Sub
Else
rst.MoveFirst
.
.
End If

If there are no records in the recordset then both rst.BOF and rst.EOF will be true. In case you are not aware you do not have to put If rst.BOF = True AND rst.EOF = True, the above is a shortened way of writing that.

[j9070749]

Thanks for that help ! The messege box works as it should now.

Anyone else know the solutions to the other problems?

[j9070749]

Just had a thought here is the current section of the code:

Set rst = db.OpenRecordset(ssql)
If rst.BOF And rst.EOF Then
MsgBox "No matches found, please try again"
Else
rst.MoveFirst
List15 = rst.Fields("ID GP")
rst.Close
End If

Would it be possible to move to the next record after 'rst.movefirst' and then display a messege box when it has finished searching. This is becuase a lot of people have a similar surname, and is only showing the first record. Also this code is 'after update' of the search textbox, would I need to add a search button in order to do the above?