Solved

COMBO BOX WITH MULTIPLE VALUE CRITERIA

Posted on 2004-03-29
3
315 Views
Last Modified: 2008-02-26
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
    Me.combo54= ""
    If Me.FilterTitle = "" Or IsNull(Me.FilterTitle) Then
    MsgBox "Please enter project keyword"
    Me.FilterTitle.SetFocus
    Else
    '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
        Else
        SQL = SQL & " And ProjectType= '" & Me.FilterProjType & "'"
        cSql = cSql & " And ProjectType= '" & Me.FilterProjType & "'"  
    End If
   
    If Me.FilterLocation = "" Or IsNull(Me.FilterLocation) Then
        SQL = SQL
        cSql = cSql
        Else
        SQL = SQL & " And LocationID= " & Me.FilterLocation
        cSql = cSql & " And LocationID= " & Me.FilterLocation
    End If
    Me.RecordSource = SQL
    Me.combo54.RowSource = cSql
    Me.combo54.Requery
    DoCmd.Save
    Me.Requery
    Me.Repaint
End If
End Sub

Thanks,

howard
0
Comment
Question by:howcheat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 10710421
Howard,
These codes are just fine.
Open the Project form on design view.
Select FilterLocation combo box and open the property sheet
Select the Event tab and remove the [Event Procedure] on the After Update.
0
 

Author Comment

by:howcheat
ID: 10711881
Capricorn1,

You're right--It works!!

My problem (I think) was the "applyfilter" subprocedure retrieved all columns of the project table which consisted of the autonumber id in column one (which I didn't want displayed), project number in column two and title in column three. Thus, combo54 which was configured for two columns displayed one and two while I actually wanted two and three to be displayed. Once I moved the autonumber id column to the third, combo54 correctly displayed the project number and the title.

Thanks again Capricorn1. You may have saved my job!!

howard
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10713174
Glad to be of help!!!

Rey:-)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question