Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Northwich, Cheshire UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using combobox as filter (Access 2000)

    Hello there,

    I am trying to use a value selected in one combo box as a filter in a following combo box. Both combo boxes are on the same form and based upon two different tables. Can anybody tell me if this is at all possible and what kind of approach would be useful in solving a problem like this.

    Any response would be helpfull,

    Thanks in advance.

    Regards,

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: using combobox as filter (Access 2000)

    Hi,
    Yes this is possible. I would use the AfterUpdate event of the first combobox to change the rowsource of the second one. If you need any help with that, please repost giving some more detail about field and table names etc.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Northwich, Cheshire UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using combobox as filter (Access 2000)

    Hello Rory,

    Thanks for your response I'll try to give a bit more detail. My first cbo is called Targets and is based upon a table called tblTargets with the fields TarCode and Target. It will allow you to choose from 10 Targets. Each of these Targets is associated with a number of Tasks. My second cbo shows all Tasks that are available. This cbo is based upon a table called tblTasks with the fields RecId, TTCode, Task and Year. The data in field TTCode corresponds with the data in field TarCode meaning that each task is linked to a specific target. What I would like to do is if I select a Target in the first cbo, l would like to see only those Tasks that are related to this Target in the second cbo.

    I hope this helps explaining what it is I try to do.

    Thanks for your help.

    Regards,

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: using combobox as filter (Access 2000)

    OK. In the AfterUpdate event of your Targets combo you could use something like the following code:
    With Me!Tasks
    .rowsource = "SELECT Task FROM tblTasks WHERE TTCode = " & Me!Targets
    .requery
    end with
    Note: I'm assuming your second combo is called Tasks, that the bound column of your Targets combo is TarCode and that you only want to return the Task name. I've also just typed this code from scratch but I think it's OK!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Northwich, Cheshire UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using combobox as filter (Access 2000)

    Hello Rory,

    Your code does help in filtering the data in the second combo box but it thinks that the '&Me!Targets' in the query is a field and after selecting a target you are prompted for a parameter value. If you type the TarCode in it does filter correctly for this code. Am I doing something wrong, as in having bound my combo boxes wrongly or am I missing a bit of coding or is this as far as it goes?

    Thanks for your help up to now.

    Regards,

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: using combobox as filter (Access 2000)

    Hi,
    This should work as is without any parameters needed. A couple of things to check:
    1. There should be a space between '&' and 'Me!Targets' (without all the quotes)
    2. Is your combobox definitely called Targets? If you look at the first line of your code, does it read:
    Private Sub Targets_AfterUpdate()
    or does it say something else?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Northwich, Cheshire UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using combobox as filter (Access 2000)

    Hello Rory,

    The first line of my code reads 'Private Sub Target_AfterUpdate()' and I do have a space between & and Me!Target.

    When I select a Target and then look at the rowsource of the second combo box it reads 'SELECT task FROM tblTask WHERE TTCode = Tas2' , the value after the equals sign varies with what you have typed in the parameter request window.

    So if you select a Target with a code Tas3, the parameter window uses Tas3 as a comment above the input line, when you enter Tas3 it selects the proper tasks but if you would enter Tas4 on the input line it would select all tasks related to code Tas4 regardless of your selected target area.

    This is what I am seeing at the moment I must be missing something but don't know what. If you or anybody else has more suggestions they would be appreciated.

    Regards,

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: using combobox as filter (Access 2000)

    Ah, sorry, I had assumed that TTCode was a numeric value - try this instead:
    <pre>.rowsource = "SELECT Task FROM tblTasks WHERE TTCode = '" & Me!Targets & "'"
    </pre>

    which will pass the Targets value as a string.
    Hope that does it.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    New Lounger
    Join Date
    Nov 2001
    Location
    Northwich, Cheshire UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using combobox as filter (Access 2000)

    Thank you very big Rory, that last bit did the trick it's working fine now <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    Regards,

Posting Permissions

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