Hi,



Can't see why this wouldn't work;



I have a text box on a form which takes a name keyed in. This is used as a 

filter in a SQL string.

ie,strSQL =  select * from Table where field like '*" & Me!txtSearch & "*'



i then pass this to rowsource in a ListBox on the same form so it will 

populate the listbox

ie, Me.ListBox.RowSource = strSQL



i can't get it to work,  any ideas????

 Thanks



by the way it works fine in Access 2.0 but not 97!

I think your quotes are out of whack--try:



strSQL =  "select * from Table where field like '*" & Me!txtSearch & "*'"



HTH,



-Roy



-----Original Message-----

From: Mike [mailto:mike.day@o...]

Sent: Wednesday, August 29, 2001 8:32 AM

To: Access

Subject: [access] Rowsource = strSQL





Hi,



Can't see why this wouldn't work;



I have a text box on a form which takes a name keyed in. This is used as a 

filter in a SQL string.

ie,strSQL =  select * from Table where field like '*" & Me!txtSearch & "*'



i then pass this to rowsource in a ListBox on the same form so it will 

populate the listbox

ie, Me.ListBox.RowSource = strSQL



i can't get it to work,  any ideas????

 Thanks



by the way it works fine in Access 2.0 but not 97!

yeah, sorry - i did have that already,



if i paste the SQL string into a query window it runs fine, i just cant get the listbox to use it as 

its rowsource..

Mike,



Have you requeried the listbox?  It must be requeried for the new info

to be displayed.  Your code should be this:



Me.ListBox.RowSource=strSQL

Me.ListBox.Requery



John Ruff - The Eternal Optimist :-)





-----Original Message-----

From: Mike [mailto:mike.day@o...] 

Sent: Wednesday, August 29, 2001 3:32 PM

To: Access

Subject: [access] Rowsource = strSQL





Hi,



Can't see why this wouldn't work;



I have a text box on a form which takes a name keyed in. This is used as

a 

filter in a SQL string.

ie,strSQL =  select * from Table where field like '*" & Me!txtSearch &

"*'



i then pass this to rowsource in a ListBox on the same form so it will 

populate the listbox

ie, Me.ListBox.RowSource = strSQL



i can't get it to work,  any ideas????

 Thanks



by the way it works fine in Access 2.0 but not 97!

this is my code and it doesn't work!! (the strSQL works OK)





Private Sub txtSearch_AfterUpdate()

  Dim strSQL



  

  DoCmd.Hourglass True



  strSQL = ""

  strSQL = strSQL + "SELECT DISTINCTROW dbo_Driver.DriverName, dbo_Driver.ChassisNumber, 

        dbo_Asset.RegNumber,dbo_Driver.ContractNumber "

  strSQL = strSQL + "FROM dbo_Driver INNER JOIN dbo_Asset ON dbo_Driver.ChassisNumber = 

               dbo_Asset.ChassisNumber "

  strSQL = strSQL + "WHERE ((dbo_Driver.DriverName Like '*" & Me!txtSearch & "*')) "

  strSQL = strSQL + "ORDER BY dbo_Driver.DriverName, dbo_Asset.RegNumber;"

  

  Me.lstSearch.RowSource = strSQL

  Me.lstSearch.Requery

    

   DoCmd.Hourglass False



end sub



This should populate the listbox with the recordset..any ideas??

Is there any difference if you change the txtSearch reference to:

  Me.txtSearch.Value

or

  Me.Controls("txtSearch").Value

?



If you debug.print strSQL and then copy/paste it into the SQL view of a new

query, does it run as expected?



The dbo prefix on the tables makes me think these are SQL server tables--is

that right?  If so, is there any difference if you use the % wildcard

character instead of *?



HTH,



-Roy



-----Original Message-----

From: Mike [mailto:mike.day@o...]

Sent: Wednesday, August 29, 2001 10:20 AM

To: Access

Subject: [access] Re: Rowsource = strSQL





this is my code and it doesn't work!! (the strSQL works OK)





Private Sub txtSearch_AfterUpdate()

  Dim strSQL



  

  DoCmd.Hourglass True



  strSQL = ""

  strSQL = strSQL + "SELECT DISTINCTROW dbo_Driver.DriverName,

dbo_Driver.ChassisNumber, 

        dbo_Asset.RegNumber,dbo_Driver.ContractNumber "

  strSQL = strSQL + "FROM dbo_Driver INNER JOIN dbo_Asset ON

dbo_Driver.ChassisNumber = 

               dbo_Asset.ChassisNumber "

  strSQL = strSQL + "WHERE ((dbo_Driver.DriverName Like '*" & Me!txtSearch &

"*')) "

  strSQL = strSQL + "ORDER BY dbo_Driver.DriverName, dbo_Asset.RegNumber;"

  

  Me.lstSearch.RowSource = strSQL

  Me.lstSearch.Requery

    

   DoCmd.Hourglass False



end sub



This should populate the listbox with the recordset..any ideas??

i reckon it's something do do with the ODBC timeout.



I'm using Linked tables- when i run it in a SQL window with ODBC timeout set  it runs fine.



I'm having trouble assigning an ODBC timeout to the strSQL im passing.

Check if it works if you use 



Me!lstSearch.RowSource = strSQL



instead of 



Me.lstSearch.RowSource = strSQL



Access 2 sometimes accepted this but Acccess 97 won't.



Chris

i managed to solve it!!



did it this way in the end







        Set currDB= CurrentDb()

        currDB.QueryTimeout = 0



        Set qdf = currDB.CreateQueryDef("TempTable", strSQL)

        lstSearch.RowSource = qdf.Name

        

        If qdf.Name = "TempTable" Then

            currDB.QueryDefs.Delete qdf.Name

        End If