Problems searching a table

[supervi]

Ok so i imported an excel sheet into a table, which I will refer to as table1.

Inside a form, i which to populate a list box with the information from the table. I can get this to work by using the following code on a command button

***********

Dim ProdSelected As String

ProdSelected = "SELECT * FROM [table1] "


Me.resultlist.RowSource = ProdSelected

End Sub
********

What i want to do is use an unbound text box to allow the user to query for a particular field in this table. So this is WHAT I THINK is the code to use.

*******
Dim ProdSelected As String


ProdSelected = "SELECT * FROM [table1] "


If Len(Me.siteid & "") <> 0 Then


ProdSelected = ProdSelected & "WHERE [table1]![Req #] = '" & Me.siteid & "'"

End If


Me.resultlist.RowSource = ProdSelected

End Sub


***********


The name of the field being searched is Req #



Obviously what i am doing doesn't work. Any help would be appreciated

 

[KenReay]

Hi

if [Req #] is a column of type numeric, you do not need the quotes

Dim ProdSelected As String


ProdSelected = "SELECT * FROM [table1] "


If Len(Me.siteid & "") <> 0 Then


ProdSelected = ProdSelected & "WHERE [table1]![Req #] = " & Me.siteid & ""

End If


Me.resultlist.RowSource = ProdSelected

End Sub

but could you be a bit more precise thatn 'does not work' are there any error messages that might give us a clue?

also it would be easier to do this by (in design view) set rowsource of the listbox to "SELECT * FROM [table1] WHERE [table1]![Req #] = Forms!MyFormName!siteid;"

and in the after update event of the control siteid put resultlist.requery





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[supervi]

genious pure genious ..it worked thanks