Expression too Complex using Combo box

[Bhoc]

I am again updating a local golf competition database and have struck an error "Expression too Complex" when using a form that contains drop down lists of players names to enter their tee off times. After entering 25 sets of 4 golfers the error shows up (No number is shown in the dialog box just the words). I have uploaded a cut down version of the database that only has the form in question and the tables that feed this form.
I have searched the forum and cannot find a solution there. Thanks in anticipation of any help :banghead:

 

[spikepl]

Show the expression. Many are reluctant/unwilling to DL a db.

 

[Bhoc]

Spikepl
Thanks for your quick response. I am not 100 percent sure which expression is causing the problem but I think it is this one -

Private Function GetSelectedPlayer(PlayerNo As Integer)
Dim dbs As Database, rst As Recordset, SQLString As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Player1ID as Player FROM tblTeeofftimesshotgun Where Player1ID<>" & PlayerNo & " " _
& "UNION SELECT Player2ID FROM tblTeeofftimesshotgun Where Player2ID<>" & PlayerNo & " " _
& "UNION SELECT Player3ID FROM tblTeeofftimesshotgun Where Player3ID<>" & PlayerNo & " " _
& "UNION SELECT Player4ID FROM tblTeeofftimesshotgun Where Player4ID<>" & PlayerNo & " ")
If Not rst.EOF Then
Do
SQLString = SQLString & "Not PlayerID=" & rst![player] & " AND "
rst.MoveNext
Loop Until rst.EOF
SQLString = Left(SQLString, Len(SQLString) - 5)
GetSelectedPlayer = "SELECT Fullname, PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, [Block Entry], [Day 1 Only] " _
& "FROM Players WHERE " _
& SQLString _
& " ORDER BY Surname"
Else
GetSelectedPlayer = "SELECT Fullname, PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, [Block Entry], [Day 1 Only] " _
& "FROM Players WHERE Scratched = 0 and [Block Entry] = -1 or [Day 1 Only] = -1" & " ORDER BY Surname"
End If
End Function

 

[JHB]

It happen because the limits of 99 And's in a Where clause exceeds.
Below is a link to MS-Access limits.
http://www.databasedev.co.uk/access_specifications.html

 

[JHB]

I've made some modification, try it now, database attached.

 

[Bhoc]

JHB and Spikepl

thanks for your help - I was just starting to redesign this part of the database to accommodate the 99 limit but thanks to you JHB that works great. You learn something new everytime you work on access.

 

[Bhoc]

JHB

Just to let you know the fix works exactly right except (there always is a but...) Before when the names appeared in the drop down they were in alphabetical order by surname - for some reason now after the alterations they appear in the order they heve been entered in the database not by surname. Help!!!!!

 

[JHB]

The add the Order By.

    Me(TheControl).RowSource = "SELECT Fullname, Players.PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, [Block Entry], [Day 1 Only] " _
    & "FROM Players LEFT JOIN NotThesePlayerId ON Players.[PlayerID] = NotThesePlayerId.[PlayerID] " _
    & "WHERE NotThesePlayerId.PlayerID Is Null[COLOR=Red][B] ORDER BY Surname[/B][/COLOR]"

The code line is in the Sub GetSelectedPlayer

 

[Bhoc]

JHB
Fantastic thanks. I was putting that line in but in wrong place thanks for all your help and quick replies

Steve

 

[Bhoc]

JHB

Am back regarding this post - have used database for recent tournament and worked great BUT and there always is a but.
When using the combo boxes to select players to tee off I am still showing the players that are not playing that day
Part of the expression as above is
Me(TheControl).RowSource = "SELECT Fullname, Players.PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, [Block Entry], [Day 1 Only] " _ & "FROM Players LEFT JOIN NotThesePlayerId ON Players.[PlayerID] = NotThesePlayerId.[PlayerID] " _ & "WHERE NotThesePlayerId.PlayerID Is Null ORDER BY Surname"

I need to select only players that have [Day 1 Only] = -1 or [Block Entry] = -1 as they are both controlled by a check box and I only require the players that are true

I have tried putting another line in
& "From Players Where [Block Entry] = -1 or [Day 1 Only] = - 1" and it doesn't like this - I am stumped can you help please.....

 

[JHB]

Could you show the whole SQL-String, (with the new criteria).

 

[Bhoc]

JHB

Here is the whole string - the part in red is what I am trying to achieve. Only want records from the players table that have true for the checkboxes Day 1 Only OR Block Entry only

Private Sub GetSelectedPlayer(PlayerNo As Integer, TheControl As String)
Dim dbs As Database, rst As Recordset, SQLString As String, RstInsert As Recordset

Set dbs = CurrentDb

dbs.Execute ("Delete * from NotThesePlayerId")
Set rst = dbs.OpenRecordset("SELECT Player1ID as Player FROM tblTeeofftimesshotgun Where Player1ID<>" & PlayerNo & " " _
& "UNION SELECT Player2ID FROM tblTeeofftimesshotgun Where Player2ID<>" & PlayerNo & " " _
& "UNION SELECT Player3ID FROM tblTeeofftimesshotgun Where Player3ID<>" & PlayerNo & " " _
& "UNION SELECT Player4ID FROM tblTeeofftimesshotgun Where Player4ID<>" & PlayerNo & " ")
Set RstInsert = dbs.OpenRecordset("NotThesePlayerId")
If Not rst.EOF Then
Do
With RstInsert
.AddNew
![PlayerID] = rst![player]
.Update
End With
rst.MoveNext
Loop Until rst.EOF
Me(TheControl).RowSource = "SELECT Fullname, Players.PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, veteran, [Block Entry], [Day 1 Only]" _
& "From Players LEFT JOIN NotThesePlayerId ON Players.[PlayerID] = NotThesePlayerId.[PlayerID] " _
& "WHERE NotThesePlayerId.PlayerID Is Null ORDER BY Surname"
& "From Players Where [Block Entry] = -1 or [Day 1 Only] = - 1"
Else
Me(TheControl).RowSource = "SELECT Fullname, PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, veteran, [Block Entry], [Day 1 Only] " _
& "FROM Players WHERE Scratched = 0 and [Block Entry] = -1 or [Day 1 Only] = -1" & " ORDER BY Surname"
End If
End Sub

 

[JHB]

...
Me(TheControl).RowSource = "SELECT Fullname, Players.PlayerID, Surname, handicap, clubabbr, cart, ncart1, scratched, veteran, [Block Entry], [Day 1 Only]" _
& "From Players LEFT JOIN NotThesePlayerId ON Players.[PlayerID] = NotThesePlayerId.[PlayerID] " _
& "WHERE NotThesePlayerId.PlayerID Is Null ORDER BY Surname"
& "From Players Where [Block Entry] = -1 or [Day 1 Only] = - 1"

You've two "From" and two "Where" in the SQL string that is the problem.
I think it should be, (but I've not tested it).

Me(TheControl).RowSource = "SELECT Fullname, Players.PlayerID, Surname,  handicap, clubabbr, cart, ncart1, scratched, veteran, [Block Entry],  [Day 1 Only][B][COLOR=Red] "[/COLOR][/B] _
    & "From Players LEFT JOIN NotThesePlayerId ON Players.[PlayerID] = NotThesePlayerId.[PlayerID] " _
    & "WHERE NotThesePlayerId.PlayerID Is Null [B][COLOR=Red]AND [Block Entry] = -1 OR NotThesePlayerId.PlayerID Is Null AND [Day 1 Only] = - 1[/COLOR][/B] ORDER BY Surname"

When you've problem paste the query string into a new query then it is easier to find the problem, when the query runs okay, then you can paste the SQL-string back into your code.

 

[Bhoc]

JHB

Seems to work great thanks
and also thanks for the tip re pasting the query etc... will keep that in mind