Why Can't I call results of sub routine in my query?

[Ajayi32]

I am using the code that follows to get a list of players names. I am putting the results (players names) in a text box on a form.

I am referencing the text box in the criteria of my query. But, the query doesn't seem to notice the results. Why? Everything works the way I want except the query not accepting it.

Private Sub Command32_Click()
Dim intSalary As Long
Dim varItm As Variant
Dim txtPlayers As String

txtPlayers = "'"
For Each varItm In qbList.ItemsSelected
txtPlayers = txtPlayers & qbList.ItemData(varItm) & "' Or '"
Next varItm
txtPlayers = Left(txtPlayers, Len(txtPlayers) - 4)
Me.qblist2 = txtPlayers

 

[KDg]

Hi Aj,

unfortunately access won't accept having a string passed in like that as part of a where clause - it will accpet having values but not "yyy OR xxx". As a side note you a WHERE caluse should take the form of
WHERE (((Table1.CustID)=1) Or ((Table1.CustID)=2))

rather than just WHERE Table1.CustID=1 Or 2, but that won't help here! I think you'd be better off building your WHERE statement in the place you're building the text box value at the moment and then making the query on the fly eg


Set dbs = CurrentDb
strsql = "SELECT Table1.CustID, Table1.Date, Table1.Value " _
& "FROM Table1 " _
& "WHERE " & YourStringHere
Set qdf = dbs.CreateQueryDef("", strsql)

Set rst = qdf.OpenRecordset

etc etc

HTH

Drew

 

[Ajayi32]

thank you, i will try that and let you know what happens

 

[rmcmorris]

Another option is to simply advance through the list and test whether or not the item is selected. If so, then add it to your result. For example,

For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
'Add Result to string
MyTeam$=MyTeam$+ListBox1.List(x, 0)
End If
Next x

 

[Ajayi32]

KDg,

I have made progress. I am however having to copy the results from the text box into the SQL view of my query to run. I thought it would run the query using this code. What am I doing wrong?

Thanks again.

This is the code now

Private Sub Command32_Click()
Dim intSalary As Long
Dim varItm As Variant
Dim txtPlayers As String
Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset

Set dbs = CurrentDb
txtPlayers = "SELECT tblPlayers.PlayerName, tblPlayers.TeamId FROM tblPlayers WHERE ( "
For Each varItm In qbList.ItemsSelected
txtPlayers = txtPlayers & "(tblPlayers.PlayerName)= '" & qbList.ItemData(varItm) _
& "' Or "
Next varItm
txtPlayers = Left(txtPlayers, Len(txtPlayers) - 4) & ");"
Me.qblist2 = txtPlayers
Set qdf = dbs.CreateQueryDef("", txtPlayers)
Set rst = qdf.OpenRecordset
End Sub

 

[KDg]

If there's no error then the SQL is probably malformed in some way. Have you tried doing debug.print txtPlayers once the string is fully built. If you cut and paste that in a standard access query it should throw out a proper error rather than just ignoring you.

Oops - just had another thought. This method won't actualy fire up the query so that you can see it. Before trying the above you could try giving the qdf a name:
Set qdf = dbs.CreateQueryDef("BillyJo", txtPlayers)
and then opening it
docmd.OpenQuery "BillyJo"

if that does fire it up then post back and i'll give you the code to get around the potential errors that creates,

HTH

Drew

 

[Ajayi32]

Yep, that does open it up now. The error I am getting after running initially is that the object "BillyJo" already exist.

I guess I can do a macro to delete it everytime?


[This message has been edited by Ajayi32 (edited 06-21-2001).]

 

[KDg]

Yup, spot on. There's a proper way to do it and a quick way. Personaly i prefer the quick way because, er, it's quick i guess.

Do do it properly you should loop through the query def collection and check each ones name, if one called "BillyJo" exists then you delete it. The quick way is to try and delete it, if it doesn't exist then it will generate the error so you just carry on from below that point -

At the top of your code, normally just under the dim statements;

On Error Goto Oops

at the bottom

exit sub
Oops:

if err.number=3011 then
  'the number for "couldn't find object..."
 resume next
else
 'something to handle other stuff
end if

 

[Ajayi32]

got, i'm good to go

thanks,

/uh