Access 2007 broke my search listbox 1

[MrKABC]

Hello all:

I have a form that populates a listbox and a text box in Access 2003/Access 2003 MDB format.

Typing a name in the text box triggers a "FindFirst" routine that attempts to match the information in the text box with a column in the list box. The user can then double click the matched information in the list box and be taken to the record.

This works fine in Access 2003, but has stopped working in Access 2007. The form works normally in 2007 with the exception of the "FindFirst" function.

Any insight as to what I am missing would be appreciated.

Here is my code:

Private Sub TxtBox_KeyUp(KeyCode As Integer, Shift As Integer)
str = TxtBox.TExt
strSQL = ListBox.RowSource

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

[COLOR=green]'[VENDORID] is the first column of the listbox and matches the information being input into the TextBox[/color]

With rs
    .FindFirst "[VendorID] LIKE '" & str & "*'"
    If rs.NoMatch Then
        Exit Sub
    Else
    i = (rs.AbsolutePosition) + 1
        ListBox.Selected(i) = True
        Exit Sub
    End If
End With

[COLOR=green]'Close the recordset and database, then reset them to nothing.[/color]
rs.Close
db.Close

Set db = Nothing
Set rs = Nothing
str = ""
End Sub

What am I missing here? What did Access 2007 break?

 

[PHV]

stopped working
What happens ? any error message ? computer crash ? unexpected behaviour ? ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrKABC]

Hi there PHV:

No crashes or anything weird. The text box/list box in Access 2003 behaves like typing in a combo box.

You start typing in the text box and the list box above it tries to match each letter with a record in the list box as you type.

In Access 2007, if you type in the text box, the list box does nothing. It doesn't respond to what you are typing. You may still manually scroll down the list box to find your value but the "search" function is now missing.

I didn't change anything - Access 2007 is running my 2003 db in "compatibility" mode. I am assuming it's a problem with the code I posted above (a syntax change maybe?)

Hope you can help!

A.

 

[PHV]

Does the event fire ?
Put a MsgBox to be sure.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrKABC]

PHV:

Yes it does fire. MsgBox comes up just fine.

A.

 

[PHV]

I'd debug the code step by step to discover what happens.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrKABC]

Problem solved:

There is nothing wrong with the code.

What I did was take the original Access 2003 MDB file, moved it to the Access 2007 machine and first repair/compacted it, then made the MDE file under Access 2007.

Presto, the list/text box code now works. Go figure. Thanks PHV for your help! =)

 

[zdas04]

Thank you, I couldn't find any reason that neither my list box selection process on a form or my link to an external picture stopped working when I (foolishly so far) went to Access 2007. Converting the db to 2007 seems to have fixed the problem.

David