Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Help find error in code - search a listbox

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    317
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Help find error in code - search a listbox

    Hey

    I need some help to search for errors in this code

    I have used this code before to search a listbox.
    But suddenly i dont get this one to work... it must be some silly error in the code, but i cant see it.
    Anyone able to help me?

    Code:
    Private Sub txtSearch_Change()
    Application.Echo False
    
      Dim strSource As String
    
      strSource = "SELECT BussElev_Table.BussElev_ID, BussElev_Table.[Elev Förnamn], BussElev_Table.[Elev Efternamn], BussElev_Table.[Elev Personnummer], Max(BussA_Table.Ansökningsdatum) AS MaxförAnsökningsdatum " & _
        "FROM BussElev_Table LEFT JOIN BussA_Table ON BussElev_Table.BussElev_ID = BussA_Table.BussElev_ID_SK " & _
        "GROUP BY BussElev_Table.BussElev_ID, BussElev_Table.[Elev Förnamn], BussElev_Table.[Elev Efternamn], BussElev_Table.[Elev Personnummer] " & _
        "Where (BussElev_Table.BussElev_ID Like '*" & Me.txtSearch.Text & "*' " _
        & "Or BussElev_Table.[Elev Förnamn] Like '*" & Me.txtSearch.Text & "*' " _
        & "Or BussElev_Table.[Elev Efternamn] Like '*" & Me.txtSearch.Text & "*' " _
        & "Or BussElev_Table.[Elev Personnummer] Like '*" & Me.txtSearch.Text & "*' " _
        & "Or BussA_Table.Ansökningsdatum Like '*" & Me.txtSearch.Text & "*') "
    
          Me.ListPicker.RowSource = strSource
          Me.ListPicker.Requery
          
    Application.Echo True
    End Sub
    I dont get any error message when running it.
    The listbox just emptys, so i guess the code is wrong and does not filter out what i want.
    Last edited by behedwin; Jun 14th, 2018 at 06:25 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2014
    Posts
    317
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help find error in code - search a listbox

    Here are some screenshots that might help

    http://prntscr.com/juw8pw
    http://prntscr.com/juw8yw

    still have not had any luck of figuring out what is wrong.

    the code in the first post is placed in a text field.
    it works in other setups jut perfectly...

    this is another code where i use it, and it works just fine
    Code:
    Private Sub txtSearch_Change()
    Application.Echo False
    
      Dim strSource As String
    
      strSource = "SELECT KeyOwnerSelect_Query.Key_ID, KeyOwnerSelect_Query.Nyckel_Kort_Nummer, KeyOwnerSelect_Query.Nyckeltyp, KeyOwnerSelect_Query.LasSystem, KeyOwnerSelect_Query.Profile_ID_SK, KeyOwnerSelect_Query.Firstname, KeyOwnerSelect_Query.Lastname, KeyOwnerSelect_Query.KeyArchived " & _
        "FROM KeyOwnerSelect_Query " & _
        "Where (KeyOwnerSelect_Query.Key_ID Like '*" & Me.txtSearch.Text & "*' " _
        & "Or Nyckel_Kort_Nummer Like '*" & Me.txtSearch.Text & "*' " _
        & "Or Nyckeltyp Like '*" & Me.txtSearch.Text & "*' " _
        & "Or LasSystem Like '*" & Me.txtSearch.Text & "*' " _
        & "Or Profile_ID_SK Like '*" & Me.txtSearch.Text & "*' " _
        & "Or Firstname Like '*" & Me.txtSearch.Text & "*' " _
        & "Or Lastname Like '*" & Me.txtSearch.Text & "*') AND KeyArchived is null "
    
          Me.ListPicker.RowSource = strSource
          Me.ListPicker.Requery
          
    Application.Echo True
    End Sub
    this code works, but not the one in the first post. I can not figure out why....

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,053
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help find error in code - search a listbox

    Possibly in your sql the WHERE clause must come before the GROUP BY clause.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Posts
    317
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help find error in code - search a listbox

    That fixed it.
    **** so easy... ive been looking at this code all day.
    An just move one row and it fixed everything :D

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •