Populate a combo box with results from SQL query

[run_GMoney]

I'm not sure exactly how to go about doing this. I was hoping the SQL would be something like:

"SELECT gameid FROM game WHERE hometeamid = " & Text1.Text & " OR awayteamid = " & Text1.Text)

That doesn't work, doesn't like breaking the "" like that. So basically, what I'm doing is creating a recordset from one table (team). The values are displayed one at a time in a textbox (Text1). A command button is used to browse through the values. I would hope that I could populate a combo box with the value(s) in another table (game) that matched the Text1.Text. Is it clear what I'm asking for? I'm not even sure if I know what I'm talking about. Anyway, if somebody has an idea of what I'm looking for I'd appreciate it. Thanks

[kleinma]

well what data types are hometeamid and awayteamid ?? if they are string you need additional ' marks around them.. like

"SELECT gameid FROM game WHERE hometeamid = '" & Text1.Text & "' OR awayteamid = '" & Text1.Text & "'")

otherwise from here the SQL statement looks ok... i could be missing something though

[techyspecy]

Originally posted by run_GMoney
I'm not sure exactly how to go about doing this. I was hoping the SQL would be something like:

"SELECT gameid FROM game WHERE hometeamid = " & Text1.Text & " OR awayteamid = " & Text1.Text)

That doesn't work, doesn't like breaking the "" like that. So basically, what I'm doing is creating a recordset from one table (team). The values are displayed one at a time in a textbox (Text1). A command button is used to browse through the values. I would hope that I could populate a combo box with the value(s) in another table (game) that matched the Text1.Text. Is it clear what I'm asking for? I'm not even sure if I know what I'm talking about. Anyway, if somebody has an idea of what I'm looking for I'd appreciate it. Thanks

Try this ..

strSql = "SELECT gameid FROM game WHERE (hometeamid = " & Text1.Text & " OR awayteamid = " & Text1.Text & ")"

Then use the variable ....

[run_GMoney]

Matt, yes the values are strings. I fixed that but got some other errors. It's probably gonna be easier to just start over I think. I gotta be doing a million things wrong. Is there a tutorial somewhere (been searching for a couple hours this morning with little success) that might allude to a way of doing this. Or perhaps someone here could help me out. What I'm not sure of is how to assign the results of an SQL query to a combo box. In general, I know that this code loops through the values in the teamid field
from the team table

Do Until rs.EOF = True 
    Combo1.AddItem rs.Fields("TEAMID") 
    rs.MoveNext
Loop

I want something similar, that checks the game.hometeamid and game.awayteamid and if either one matches the value from the team.teamid (that's currently displayed in the Text1 textbox), it adds the item to the combo box.....inhale....exhale....ok.

Do Until rs.EOF = True
    Combo1.AddItem results from SQL query???
    rs.MoveNext
Loop

I guess that's my main problem. Also do I need to set up a seperate Recordset if I'm working with 2 different tables? Clueless at this point. Thanks for the help so far, though.

[run_GMoney]

Hey new error here. I'm not sure if I'm doing this right so feel free to correct me here. I've declared a recordset and am trying to assign the result of an SQL query to it:

Set rs = New ADODB.Recordset
 
rs.Open "SELECT * FROM GAME WHERE HOMETEAMID = '" & Text1.Text & "' OR AWAYTEAMID = '" & Text1.Text & "'"

It was my hope that I could then loop through these results and use them to populate my combo box. Not so much. I get an error:

Run-time error '3709';

The connection cannot be used to perform this operation. It is either
closed or invalid in this context.

I must be using it in the wrong context. So what the heck is the right context??