Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Need help with a listbox and combo box to filter records

    Hello, I have been having problems for a few days now trying to filter a listbox based on a combo box (and if nesessary a textbox) on a form.

    The two choices from the listbox are "search for businesses" and "search for community groups". Depending on which choice is selected I would like the corresponding row source to be displayed from either "tblCommunityGroups" or "tblBusinesses".

    The form is called "frmSearchTest" and the combo box is combo3, and its After Update event has the following code -

    Code:
    Dim strRowSource As String
    
    If Me.Combo3.Value = "search for businesses" Then
    strRowSource = "SELECT tblBusinesses.businessID, tblBusinessesName FROM tblBusinesses WHERE (CStr(tblBusinesses.businessID)) Like '*" & Forms!frmSearchTest![Text5] & "*' And ((tblBusinesses.businessName) Like '*" & Forms!frmSearchTest![Text5] & " *'));"
    Me.List0.Requery
    Else
    
    If Me.Combo3.Value = "search for community groups" Then
    strRowSource = "SELECT tblCommunityGroups.comGroupID, tblCommunityGroups.groupName FROM tblCommunityGroups WHERE (CStr(tblCommunityGroups.comGroupID)) Like '*" & Forms!frmSearchTest![Text5] & "*' And ((tblCommunityGroups.groupName) Like '*" & Forms!frmSearchTest![Text5] & " *'));"
    Me.List0.Requery
    End If
    End If
    
    Me.List0.RowSource = strRowSource
    I cannot make the listbox display anything, its just blank. No errors when I make a selection from the combo box, just nothing.

    I also have a text box on the form (Text5) which when the selection is working I want to be able to type in either an ID or letter or word to further narrow the results displayed in the text box. For example, if I type in the letter "t" I get all the results with the letter "t" in them. Again, I cannot get it to work, no errors either. I will execute the textbox search by a command button with list0.requery in the on click event.

    Whats wrong with my code because nothing is working?


  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Replace the "AND" in your WHERE clauses with "OR".
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Thanks, but I had already tried changing the AND to OR and it makes no difference to the listbox remaining empty.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Hmm. Post some sample data from tblBusinesses and tblCommunityGroups, and I'll see if I can replicate the problem.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Hi, I have zipped up the database which is probably the easiest thing to do.

    The form is called "frmSearchTest"

    First, make a selection from the combo box to filter which table source the listbox (list0) displays.

    Secondly enter a number e.g. 1, or 2 or some text eg abc, def into the textbox and click on the search command button.

    The listbox0 requeries as empty, so something isnt quite right with my command button on-click code.

    Cheers!
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Here is your problem:
    Code:
    If Me.Combo3.Value = "search for community groups" Then
    strRowSource2 = "SELECT tblCommunityGroups.comGroupID, FROM tblCommunityGroups WHERE (CStr(comGroupID)) Like '*" & Text5 & "*';"
    Me.List0.Requery
    End If
    You have a comma between the SELECT clause and the FROM clause. I found this by stepping through the code and outputting the contents of strRowSource2 to the immediate window - it's a useful trick!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Haha!! Well spotted! I had stepped through it, but clearly had missed the unnesessary comma.

    Thank you so much, you are brilliant

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You're welcome!

    I'm not brilliant - just a fresh pair of eyes. God knows I've done similar things myself in the past...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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