Make Search textbox highlight correct record on my LISTbox

[Deisun]

Hi guys,



I want my List to highlight what item I searched for. I know I need to use:
Listbox.Selected()

But I cannot figure out how to incorporate it with my Search box.

Here's my Searchbox code that's in the AfterUpdate:

Private Sub txtGoTo_AfterUpdate()
    'search firstname, lastname, phone # 1, phone # 2
    Dim strWhere As String

    If Not IsNull(Me.txtGoTo) Then
    If Me.Dirty Then 'Save before move
    Me.Dirty = False
    End If

    strWhere = "([First Name] = """ & Me.txtGoTo & _
    """ OR [Last Name] = """ & Me.txtGoTo & _
    """ OR [Phone # 1] = """ & Me.txtGoTo & _
    """ OR [Phone # 2] = """ & Me.txtGoTo & """)"
    
    With Me.RecordsetClone
    .FindFirst strWhere
    If .NoMatch Then
    MsgBox "Not found."
    Else
    Me.Bookmark = .Bookmark
    End If
    End With
    End If
End Sub

Would anyone be so kind as to show me how to get my Listbox to highlight the record my Search finds?

I have:

Listbox34.Selected(#######) = true

What can I put in place of the ###### to get the record number that it found?

[rpeare]

Dim db As Database
Dim ssql As String
Dim rst As Recordset
Dim strWhere As String

Set db = CurrentDb

strWhere = "(((FieldName1) like '*" & Me.txtGoTo & "*') OR ((FieldName2) like '*" & Me.txtGoTo & "*') OR ((FieldName3) like '*" & Me.txtGoTo & "*'))"
ssql = "SELECT * FROM TableName WHERE " & strWhere

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

db.Close

Try this. Anyplace you see the word NAME you are going to have to substitute in your form, table or field names except for the ID field which is the primary key of the item in the list box.

[Deisun]

So just throw all that in my AfterUpdate event?

This is the code I came up with after swapping in my correct field names (i think). It doesnt work though. When I do a search it doesn't come up with anything.

Private Sub Text37_AfterUpdate()
    Dim db As Database
    Dim ssql As String
    Dim rst As Recordset
    Dim strWhere As String

    Set db = CurrentDb

strWhere = "((([First Name]) like '*" & Me.txtGoTo & "*') OR (([Last Name]) like '*" & Me.txtGoTo & "*') OR (([Phone # 1]) like '*" & Me.txtGoTo & "*'))"
ssql = "SELECT * FROM Clients WHERE " & strWhere

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

db.Close



End Sub

[rpeare]

or the ON EXIT property (which is where I tested it)

[Deisun]

Doesn't work

[rpeare]

It does work :P You've just probably not converted the code correctly.

Does your CLIENTS table actually have primary key field called ID? if not

Listbox34 = rst.Fields("ID")


this is your problem. you have to change the ID to the name of your field that contains the primary key

[Deisun]

Table: Clients
Fields: ID, First Name, Last Name, Phone # 1, Phone # 2

That's all I have.
I don't know why it wont work for me :/

[rpeare]

post a sample of your database with garbage data in it then.

[Deisun]

Not sure how to do that, you mean like a screenshot of the table?

[rpeare]

make a copy of your database, take out any information that may be considered sensitive (or change it so it's masked like changing people's names), zip it up and click on the MANAGE ATTACHMENTS button below your message creation area then just navigate to where the zip file is and click upload.

[Deisun]

Ok thanks for all your help so far. I really appreciate it.

I've attached a sample of my DB.

[rpeare]

I won't be able to view this until wednesday in access 2007 format if you could upload a 2003 version I can, or someone else can take a look it before then.

[uronmapu]

Is this case fix????

Could you upload file is fine ????


Many thanks