Thread: using combo boxes to filter data
11-11-08, 07:18 #1Registered User
- Join Date
- Nov 2008
Unanswered: using combo boxes to filter data
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:
Like "*" & [Forms]![frmCompanies]![PostcodeSelector] & "*"
Private Sub PostcodeSelector_Change() Me.Refresh End Sub
Like "*" & [Forms]![frmCompanies]![Search2] & "*"
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
11-11-08, 08:02 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
me.filter = "mystringcolumnname = " & chr$(34) & mylistbox.text & chr$(34)
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 filtersI'd rather be riding on the Tiger 800 or the Norton
11-11-08, 19:07 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
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 19:12.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)