Find the error - searching in a listbox does not work?

[behedwin]

Hi

I need some help find a error in my code.
I have been banging my head against this for some time now.

I have a textbox that is called txtSearch
It is supposed to search in a listbox. So when the user typse in the textbox, the result filters down in the listbox. This code have worked many times but i can not figure out why this one is not working now.
Listbox is called: ListPicker

My SQL for the listbox is this when form is being loaded.

SELECT  SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname,  SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID,  SelecBuss_Query.Uttryck1
FROM SelecBuss_Query
GROUP BY  SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname,  SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID,  SelecBuss_Query.Uttryck1
ORDER BY SelecBuss_Query.Arskurs;

As you can see it is based on a Query
The Query looks like this:

SELECT  BussElev_Table.ElevFirstname, BussElev_Table.ElevLastname,  BussElev_Table.Arskurs, BussElev_Table.BussElev_ID, Max([Ansokningsdate]  & " " & [kommentar]) AS Expression1
FROM BussElev_Table LEFT JOIN BussA_Table ON BussElev_Table.BussElev_ID = BussA_Table.BussElev_ID_SK
GROUP BY BussElev_Table.ElevFirstname, BussElev_Table.ElevLastname, BussElev_Table.Arskurs, BussElev_Table.BussElev_ID
ORDER BY BussElev_Table.Arskurs;

Then finaly i have the VBA code that runs the search.
It looks like this

Application.Echo False

  Dim strSource As String
  
  
  
   strSource = "SELECT SelecBuss_Query.ElevFirstname,  SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs,  SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1 " & _
    "FROM SelecBuss_Query " & _
    "Where (BussElev_Table.ElevFirstname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or BussElev_Table.ElevLastname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or BussElev_Table.Arskurs Like '*" & Me.txtSearch.Text & "*' " _
    & "Or BussElev_Table.BussElev_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "Or BussElev_Table.Expression1 Like '*" & Me.txtSearch.Text & "*' " _
    & "ORDER BY SelecBuss_Query.Arskurs; "
     '& "GROUP BY SelecBuss_Query.ElevFirstname,  SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs,  SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1 "

      Me.ListPicker.RowSource = strSource
      Me.ListPicker.Requery

Me!ListPicker = ""
Application.Echo True
End Sub

Anyone able to help me solve this.

I have other codes exaclty like this that do work.
But this one just cleares my listbox and it is empty.
I can not understand why.

 

[behedwin]

Pasted in the wrong SQL in the first code
this is the correct one

SELECT SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1
FROM SelecBuss_Query
GROUP BY SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1
ORDER BY SelecBuss_Query.Arskurs;

and also pasted in the wrong VBA code

this is the correct one:

Private Sub txtSearch_Change()
Application.Echo False

  Dim strSource As String
  
  
  
   strSource = "SELECT SelecBuss_Query.ElevFirstname,  SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs,  SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1 " & _
    "FROM SelecBuss_Query " & _
    "Where (SelecBuss_Query.ElevFirstname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.ElevLastname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.Arskurs Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.BussElev_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.Expression1 Like '*" & Me.txtSearch.Text & "*' " _
    & "ORDER BY SelecBuss_Query.Arskurs; "
     '& "GROUP BY SelecBuss_Query.ElevFirstname,  SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs,  SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1 "

      Me.ListPicker.RowSource = strSource
      Me.ListPicker.Requery

Me!ListPicker = ""
Application.Echo True
End Sub

 

[welshgasman]

Nothing jumps out, but whatever you are asking for, is not present if it is working?
Debug.Print strsource, copy and paste that into a sql window

Also requery not required if you change the rowsource.

HTH

 

[stumac]

& "ORDER BY SelecBuss_Query.Arskurs[B][COLOR=#ff0000];[/COLOR][/B] "

try removing the semi colon - this ends the SQL statement but you continue to add a GROUP BY after it.

Also - all your fields are coming from one query - I find removing the query name as a qualifier look a lot easier to read and helps find small errors like this. One other thing, you are searching all the fields for one string, you can concatenate the string and search it once:

strSource = "SELECT ElevFirstname,  ElevLastname, Arskurs,  BussElev_ID, Expression1 " _
 & "FROM SelecBuss_Query "  _
 & "Where (ElevFirstname & ElevLastname & Arskurs & BussElev_ID & Expression1 Like '*" & Me.txtSearch.Text & "*' " _
 & "ORDER BY Arskurs " _
 & "GROUP BY ElevFirstname,  ElevLastname, Arskurs,  BussElev_ID, Expression1 "

 

[welshgasman]

The Group By is commented out?

 

[xenou]

Doing as suggested by welshgasman in post 3 I get the following sql as your strsource:

SELECT 
	SelecBuss_Query.ElevFirstname,
	SelecBuss_Query.ElevLastname, 
	SelecBuss_Query.Arskurs,
	SelecBuss_Query.BussElev_ID,
	SelecBuss_Query.Expression1 
FROM 
SelecBuss_Query 
WHERE 
	(
	BussElev_Table.ElevFirstname Like '*TEXT_TO_SEARCH*' 
	Or BussElev_Table.ElevLastname Like '*TEXT_TO_SEARCH*' 
	Or BussElev_Table.Arskurs Like '*TEXT_TO_SEARCH*' 
	Or BussElev_Table.BussElev_ID Like '*TEXT_TO_SEARCH*' 
	Or BussElev_Table.Expression1 Like '*TEXT_TO_SEARCH*' 
ORDER BY 
	SelecBuss_Query.Arskurs;

Some formatting is added by me to make it clearer (carriage returns and indentation).
But essentially two things jump out:

One is that there is no closing right parenthesis on the WHERE clause which is a fatal syntax error.
Two is that it isn't clear if you really have a field called Expression1 or not. If not, then that is another serious problem.

Note that I suppose it is up to you if you intended to have a group by or not. Here I assume no group by is intended.