VBA and SQL

[James Pye]

Hi everyone,

I am using VBA & SQL to try and filter/order data in a sub form.

I am getting the following error:

Run-time error '3079':
The specified field 'Code' could refer to more than one table listed in the FROM clause of your SQL statement.

Here is the relevant VBA code:

Private Function Sorting(Optional col As String, Optional fil As String) As Integer
Dim strSQL As String
Dim strPosition As String
Dim strClub As String
Dim sngPrice As Single
strPosition = cboPosition
strClub = cboClub
sngPrice = cboPrice
    strSQL = "SELECT tlkp_PlayerList.Position, tlkp_PlayerList.Code, tlkp_PlayerList.Name, tlkp_PlayerList.Club, FORMAT(tlkp_PlayerList.Price,'£.0m') AS Price, "
    strSQL = strSQL & "tlkp_PlayerStatus.Status, tlkp_PlayerStatus.Description, tlkp_PlayerStatus.EstimatedReturn FROM tlkp_PlayerList "
    strSQL = strSQL & "LEFT JOIN tlkp_PlayerStatus ON tlkp_PlayerList.Code=tlkp_PlayerStatus.Code "
    
        Select Case strPosition
            Case "All"
                strSQL = strSQL & "WHERE Position <> '' AND Club "
                    If strClub = "All" Then
                        strSQL = strSQL & "<> '' "
                    Else
                        strSQL = strSQL & "= '" & strClub & "' "
                    End If
                    
                    If sngPrice = 0 Then
                        strSQL = strSQL & "AND Price <> 0"
                    Else
                        strSQL = strSQL & "AND Price = " & sngPrice
                    End If
            Case "Goalkeepers"
                strSQL = strSQL & "WHERE Position = 'GK' AND Club "
                    If strClub = "All" Then
                        strSQL = strSQL & "<> '' "
                    Else
                        strSQL = strSQL & "= '" & strClub & "'"
                    End If
                    
                    If sngPrice = 0 Then
                        strSQL = strSQL & "AND Price <> 0"
                    Else
                        strSQL = strSQL & "AND Price = " & sngPrice
                    End If
            Case "Defenders"
                strSQL = strSQL & "WHERE Position = 'DEF' AND Club "
                    If strClub = "All" Then
                        strSQL = strSQL & "<> '' "
                    Else
                        strSQL = strSQL & "= '" & strClub & "'"
                    End If
                    
                    If sngPrice = 0 Then
                        strSQL = strSQL & "AND Price <> 0"
                    Else
                        strSQL = strSQL & "AND Price = " & sngPrice
                    End If
            Case "Midfielders"
                strSQL = strSQL & "WHERE Position = 'MID' AND Club "
                    If strClub = "All" Then
                        strSQL = strSQL & "<> '' "
                    Else
                        strSQL = strSQL & "= '" & strClub & "'"
                    End If
                    
                    If sngPrice = 0 Then
                        strSQL = strSQL & "AND Price <> 0"
                    Else
                        strSQL = strSQL & "AND Price = " & sngPrice
                    End If
            Case "Strikers"
                strSQL = strSQL & "WHERE Position = 'STR' AND Club "
                    If strClub = "All" Then
                        strSQL = strSQL & "<> '' "
                    Else
                        strSQL = strSQL & "= '" & strClub & "'"
                    End If
                    
                    If sngPrice = 0 Then
                        strSQL = strSQL & "AND Price <> 0"
                    Else
                        strSQL = strSQL & "AND Price = " & sngPrice
                    End If
        End Select
        
    strSQL = strSQL & " ORDER BY " & col
    Me.fsub_PlayerList.Form.RecordSource = strSQL
    
End Function

Can anyone see where I am going wrong?

Many thanks

 

[spikepl]

Probably this line (since the rest looks OK)
strSQL = strSQL & " ORDER BY " & col

when you get into troubles like these, then always checlk what the content of the sql is:

debug.print strSQL