Something wrong with the SQL-line in VBA

[kuifje_007]

What is wrong with this line?

.Source = "SELECT Player, Seed, Tournament FROM tblDraw WHERE Tournament=" & [Forms]![frmTournament]![cboTournament].Value

I wanted if you click the combobox cboTournament, all the datas of Tournament from tblDraw will appear.
The program stopped at here.

 

[WayneRyan]

kuifje,

You didn't say what the error message was and what was highlighted.

1) .Source =

.Source of What?

2) =" & [Forms]![frmTournament]![cboTournament].Value

If cboTournament contains a string then you need:

= '" & [Forms]![frmTournament]![cboTournament] & "'"

3) =" & [Forms]![frmTournament]![cboTournament].Value

Is the ComboBox multicolumn? If it is, you need something like:

= " & [Forms]![frmTournament]![cboTournament].Column(1)

Need more info. Can you post the code?

Wayne

 

[kuifje_007]

Thanks for your reply.

I have a table tblDraw with the fields PlayerID, Player, Seed and Tournament, and tblTournament with fields WinnerID, Winner and Tournament.

I made a form which will write down to these tables. And if you select a certain tournament, the tournament should retrieve the datas again. But it didn´t.

I got an error message, but I can´t translate it well because it is Dutch: syntaxis error (operator missed) in query-epression Tournament.

Here is the code:

Sub RetrieveTournamentTable()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnn = CurrentProject.Connection

With rst
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Source = "SELECT Player, Seed, Tournament FROM tblDraw WHERE Tournament =" & [Forms]![frmTournament]![cboTournament].Value
.Open
End With

If rst.EOF = False Then

For i = 1 To 32

[Forms]![frmTournament]("cboPlayer" & i).Value = rst!Player
[Forms]![frmTournament]("txtSeed" & i).Value = rst!Seed
[Forms]![frmTournament]![cboTournament].Value = rst!Tournament

Next i

rst.Close

With rst
.ActiveConnection = CurrentProject.Connection
.LockType = adLockPessimistic
.CursorType = adOpenKeyset
.Source = "SELECT Winner, Tournament FROM tblTournament WHERE Tournament= [Forms]![frmTournament]![cboTournament].Value
.Open
End With

For j = 1 To 31

[Forms]![frmTournament]("cboWinner" & j).Value = rst!Winner
[Forms]![frmTournament]![cboTournament].Value = rst!Tournament

Next j

rst.Close

End If

End Sub

 

[WayneRyan]

K,

This is the statement that it doesn't like:

[Forms]![frmTournament]![cboTournament].Value

When you get the error, go to View --> Immediate Window

Then type:

?[Forms]![frmTournament]![cboTournament].Value

and it will print out something. What does it print?

Wayne

 

[kuifje_007]

It says he cannot find frmTournament.

 

[kuifje_007]

I think it is solved now thanks to:

= '" & [Forms]![frmTournament]![cboTournament] & "'"


Thank you, Wayne!

 

[kuifje_007]

It works good but not at all. If you select Tournament (cboTournament) which you haven't selected the comboboxen (cboPlayer and cboWinner) yet, those comboboxen are empty, which is good. And if you have selected the comboboxen of a certain tournament and then select another Tournament, the comboxen of the last Tournament still stay. It should be empty, so maybe 'SELECT FROM WHERE" is not a good solution. Hadn't I better use .Filter in 'With .rst'? But I tried and it doesn't work.

 

[kuifje_007]

I think I need to refresh. But I don't know how and where.

Something like:

Forms("frmTournament").Refresh ?

Well, he still doesn't work.

 

[WayneRyan]

K,

Use the AfterUpdate event of the first combobox:

Me.OtherComboBox.Requery

Wayne

 

[kuifje_007]

You mean in the combobox cboTournament?

I tried it like:


Private Sub cboTournament_AfterUpdate()

For i = 1 To 32

[Forms]![frmTournament]("cboSpeler" & i).Requery

Next

For j = 1 To 31

[Forms]![frmTournament]("cboWinner" & j).Requery

Next

End Sub


So I have 32 comboboxen (cboPlayer) for the first round (Draw) and 31 comboboxen (cboWinner) for the next rounds (2nd Round, Quarterfinal, semi-final, final and winner). They all have to be refreshed after I select on the certain Tournament.