Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Unanswered: Listbox Event has to called Twice

    Hello,

    I am having a problem with a listbox in Access 2010 where I need to hit "Return/Enter" twice in order to get the desired results of the SQL query.
    I hit enter the first time and get the results from the previous query, then I have to hit it again and then it shows what I want. I would like to just hit enter once. Thank you for your help.

    I've tried adding a Me.Subform!lstSearchResult.Requery after the setting the rowsource but that doesn't help either. It's like its not getting refreshed after the rowsource SQL is set.

    Code:
    Private Sub txtHeader_Search_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then Call cmdSearch 'Fires search event after pressing enter in txtHeader_Search
    End Sub
    ----------------------------------------------------------------
    Private Sub cmdSearch()
        On Error GoTo ErrorHandler
    
    Call cboHeader_ClientNameOUT
    
    SQL = _
          "SELECT CL.cl_Name, CL.cl_ID, " & _
                 "CN.cn_First & ' ' & CN.cn_Last AS ContactName, CN.cn_ID, " & _
                 "CM.cm_ShortName , CM.cm_ID " & _
            "FROM ((Company AS CM " & _
             "INNER JOIN Contact AS CN " & _
              "ON CM.cm_ID = CN.CM_ID) " & _
             "INNER JOIN Client AS CL " & _
              "ON CN.cn_ID = CL.cl_MainContact) " & _
           "WHERE cl_Name LIKE '*" & Me.txtHeader_Search.Value & "*' " & _
              "OR (cn_First & ' ' & cn_Last) LIKE '*" & Me.txtHeader_Search.Value & "*' " & _
              "OR cm_Name LIKE '*" & Me.txtHeader_Search.Value & "*' " & _
           "ORDER BY cl_Name;"
    
    Me.Subform.SourceObject = "sfrmSearchResult"
    Me.Subform!lstSearchResult.RowSource = SQL
    
    
    
    GoTo CleanUpAndExit
    '-------
    CleanUpAndExit:
    Exit Sub
    
    ErrorHandler:
        MsgBox Err.Description & vbCrLf & Err.Number
        
    Resume CleanUpAndExit
    
    End Sub
    Last edited by akaisar; 08-08-12 at 11:32.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so i'd suggest you put some watches on the various events, step through the code and understand what is happening (ie when which events are triggered and with with values)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2012
    Posts
    3
    Thank you. I did what you said and have found the problem. I had stepped through the code numerous times before, but I checked the value of Me.txtHeader_Search.Value and found that because of the KeyDown event fires first before the .Value is updated I needed to change it to .Text instead.

    Thank you again for your help.


  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Hay my teacher bit coming out of me here

    That SQL is hard to read

    What I teach and do

    SQL = ""
    SQL ="select something FROM somewhere WHERE something = '[bla]'"
    then

    SQL = replace(SQL,"[bla]",me.thistextfeild)

    Now is that easier to read lol
    Last edited by myle; 08-09-12 at 03:34. Reason: Spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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