Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: using combo boxes to filter data

    Hi,
    I've created a database that allows you to search through the records and which displays more in-depth information of each record on the right hand side once you click on any item. I am however hoping to have one more function which is to choose a selection from a combo box, which will then filter the data by that selection. I am having some problems though and was wondering if anyone could offer me an alternative suggestion or tell me where my problem may lie???

    The listbox which contains the data I wish to filter has this data in the row source for the combo box:

    Code:
    Like "*" & [Forms]![frmCompanies]![PostcodeSelector] & "*"
    This is for the PostcodeSelector combo box. The combo box then has this code:

    Code:
    Private Sub PostcodeSelector_Change()
    
    Me.Refresh
    
    End Sub
    I was thinking this would be the simplest way of doing this but all it seems to do when I choose a selection in the combo box is refresh the page. Is this because of some of my other code? My search box searches through the records in my listbox using:

    Code:
    Like "*" & [Forms]![frmCompanies]![Search2] & "*"
    It also uses the following code:

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub listdisplay_AfterUpdate()
    
          Dim rs As Object
    
        Set rs = Me.RecordsetClone
        rs.FindFirst "[URN] = " & Str(Me![listdisplay])
        Me.Bookmark = rs.Bookmark
    
    End Sub
    
    Private Sub Search_Change()
    Dim vSearchString As String
    
     vSearchString = search.Text
     Search2.Value = vSearchString
     Me.listdisplay.Requery
    
    End Sub
    Any help would be greatly appreciated!!

    Russ

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Assumign you are usign a listbox.....
    you may be better off using the on click event which then either sets a filter or requeries the backing data.

    me.filter = "mynumericcolumnname = " & mylistbox.value
    OR
    me.filter = "mystringcolumnname = " & chr$(34) & mylistbox.text & chr$(34)
    me.filteron=true

    as usual this is air code, made up ont he fly it probably won't compile, it maybe wrong, but it it should give you some pointers.. check the syntax for applying filters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I would use the approach where in the AfterUpdate event of the listbox (otherwise using OnClick can give you an error if you don't exactly on the row of data), you then requery (ie. me.requery) or set the recordsource (ie. me.recordsource = "Select * from MyTable where <somefield> like "*" & Forms!MyFormName!MyListBoxName & "*""

    In the listbox properties for the Bound column, make sure the bound value matches up with the field you're using in the criteria.

    I don't really like using the .filter command as I've had so many problems with it (when certain syntax such as ' was entered) in the past.
    Last edited by pkstormy; 11-11-08 at 20:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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