onkeypress changing the rowsource of a list 1

[drkhelmt]

I have a form to look up a part. So I have a text box where the user enters part of a part number to limit the list. On this text box, I have the following code on the onkeypress event:

    Dim sqlst As String
    sqlst = "SELECT part.partNumber, part.partDesc " _
        & "FROM part " _
        & "WHERE (((part.partNumber) Like " & Chr(34) & Me.partNo_txt & "*" & Chr(34) & "));"
    Me.part_lst.RowSource = sqlst
    Me.part_lst.Requery

This seems to create the sql fine, however, when the user enters the first leter of a part number, the list goes blank.

There is probably a stupidly simple solution, I just can't find it. Any help is appreciated.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[Zion7]

Use the ON CHANGE event of the text box, and use the TEXT property of the text box, not the default property which is VALUE.
Private Sub partNo_txt_Change()
Dim sqlst As String
sqlst = "SELECT partNumber, partDesc " _
& "FROM part " _
& "WHERE (((partNumber) Like " & Chr(34) & Me.partNo_txt.Text & "*" & Chr(34) & "))"
Me.part_lst.RowSource = sqlst
Me.part_lst.Requery

PS, semicolons at the end of SQL statements are not necessary, or repeating the tablename every time you reference a field....just for the record

 

[drkhelmt]

Perfect! Works like a charm! I knew it was simple... Isn't the semicolon requred for sql? It may not be when dealing with Access, but I thought it was in strict SQL?

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?