COMBO BOX WITH MULTIPLE VALUE CRITERIA
Posted on 2004-03-29
I have a form with a combobox (combo54) that contains project numbers and project titles. A selection in the combobox pulls up the applicable record in both the form and subform. The code below pertains to a series of filter combo boxes (filterlocation, FilterProjType ) which should drill down and filter the selection in combo54. The first portion of the code is an unbound text box (filtertitle)where users enter a keyword of the project title, click apply, and the related projects are filtered in combo54. This works fine but the users want to take the filterlocation & filterprojtype combo boxes and filter the selection in combo54 even further and that's where I have a problem. The filter works but the records that should be filtered out still appear in combo54. AN EXAMPLE: Upon opening the form, user enters a key term(let's say RPL) in filtertitle textbox, clicks the applyfilter button(below) and all records with the term RPL appear in combo54. But combo54 has 4 records with two different locations Yokota and Misawa so the combobox filterlocation of location is used to select Misawa and only records from Misawa should be displayed in combo54. This partially works as the command button with acnext will display the Misawa records one after the other but the Yokota records are still displayed in combo54. I can select both Misawa records in combo54 and the appropriate record appears but the 2 Yokota entries are also still present in combo54. How can I get rid of this records??? My code is present below:
Private Sub ApplyFilter_Click()
Dim Str As String, SQL As String
Dim cSql As String
If Me.FilterTitle = "" Or IsNull(Me.FilterTitle) Then
MsgBox "Please enter project keyword"
'Str = Me.FilterTitle
SQL = "SELECT * FROM Project WHERE ((Project.Title) Like '" & Me![FilterTitle] & "*" & "'" & ")"
cSql = "SELECT * FROM Project WHERE ((Project.Title) Like '" & Me![FilterTitle] & "*" & "'" & ")"
If Me.FilterProjType = "" Or IsNull(Me.FilterProjType) Then
SQL = SQL
cSql = cSql
SQL = SQL & " And ProjectType= '" & Me.FilterProjType & "'"
cSql = cSql & " And ProjectType= '" & Me.FilterProjType & "'"
If Me.FilterLocation = "" Or IsNull(Me.FilterLocation) Then
SQL = SQL
cSql = cSql
SQL = SQL & " And LocationID= " & Me.FilterLocation
cSql = cSql & " And LocationID= " & Me.FilterLocation
Me.RecordSource = SQL
Me.combo54.RowSource = cSql