Filtering the dropdown list in a combobox

[someboddy]

Lets say I have a table:

Where "petID" is ofcourse the pk, "name" is the name of the breed, and "type" is the species of the pet(dog or cat). There is also another table - "Cages" - with two fields - "cageID" as the pk, and "pet" which is linked to "Pets.petID".

Now, I've made a continuous form for "Cages". The form contains a textbox for "cageID", and a combobox for "pet" - which takes it's values from "Pets". So far, quite simple:

Now, the problem begins when I want to filter the dropdown list of the combobox - lets say to contain just dogs. so I change it's rowsource:

cbo_pet.RowSource = "SELECT Pets.petID, Pets.name FROM Pets WHERE Pets.type='dog'"
cbo_pet.Requery

This is what I get when I run that code:

All the cats are gone! I know I filtered them out, but I did this because I want easier access to the dogs in the dropdown list - I still want cages that contain cats to display the cat breed!

So, is there a way to solve this? To filter out rows from the dropdown list, yes still have them displayed if they are selected?

If it isn't possible, I thought of a few workarounds for this:

1. Sort instead of filter. I just sort it so all the dogs will be on the top of the list, so the user can easily access them. I think it's a BAD solution, but I decided to write it just in case... maybe there is a way to cut the dropdown list in the middle?
2. Cover the combobox with a textbox. I create a textbox with the name of the breed, and locate it over the combobox so only the dropdown button is visible. The only problem in this solution is that it forces the user to use the dropdown list - they can't just write inside the combobox and have Access autocomplete it for him... however, they can use the autocomplete method of inserting values if they click the dropdown button first and then start typing the name of the breed.
3. Add an extra row the dropdown list of each combobox, containing the value already in there. That way, even if it's in a group that's supposed to be filtered out, it will still be in the list, and will be displayed.
   Problem is - I'm using a continuous form, so if I change the RowSource of one combobox - it changes the RowSource of them all. Is there a way to access that property individually? I've tried googling it out, but all I can find is stuff about conditional formatting...

Thanks in advance!

 

[namliam]

I cannot see the images, but this is what I understand you want to do....
You want to see all the animals that can be carried in cages, as long as the cage can carry a dog...

You would have to change your query a bit.... instead of searching for "dog" you need to be searching for cages that can cary a dog.
So instead of a query like so
select * from yourcagetable where type = "dog"
you need something like so:
Select * from yourcageTable where cageid in (select Cageid from yourcagetable where type = "dog")

 

[someboddy]

No no no... you misunderstood.

I want all the cages to be displayed, regardless of what animal is inside. Since there are going to be hundreds of rows(the animals are just for example), I don't want to allow the user to filter the dropdown list - instead of it containing all types of animals, they will be able to choose if they want it to contain only dogs, only cats, only birds, only lizards, only fish(it's a bad idea to put a fish in a cage. The bars can't stop the water...) etc.

 

[DCrake]

Also using field names such as Name and Type is only going to confuse matters even more. These are Access reserved words and as such should be avoided when choosing fieldnames. See reserved words and naming conventions.

David

 

[namliam]

You want a "show all" option... correct?

 

[someboddy]

You want a "show all" option... correct?

What do you mean by that?

 

[namliam]

I mean you have a filter box, that filters cages... this causes your form to only show a filtered down set of cages.

However you want the option to show all cages...

 

[someboddy]

Showing all the cages is not the problem. The problem is showing the filtered out animals in the cages that contain them.

 

[namliam]

So you want to show "dog" or "Cat" on the cage line? Then why not simply add the type column to the form?

 

[someboddy]

That's not it either... I think you'll understand better if you see the pictures. Do you have a problem with imageshack?

 

[namliam]

Hosting sites like imageshack are filtered by the firewall

I am stumped then sorry

 

[someboddy]

OK, I've uploaded the images as attachments. "petsTable.png" is the table "Pets". "cagesForm.png" is the continuous form representing the cages. "filteredCagesForm.png" is the same form, after filtering the cats out. I only wanted to filter the cats out of the dropdown list in the comboboxes, not from begin displayed in the textbox part of the combobox...

 

[namliam]

Unfortunatly what you want is not possible, your option 2 is the only viable workaround.

The problem is that the combobox's visible are one and the same, only with different values. Change one, change all.

 

[DCrake]

Looking at your initial posts it appears that by using a continuious form and having a recordsource for the combo fitlered type = Dog will apply to each row in the form as the desing view of the form only shows 1 combo.

David

 

[someboddy]

So, it's not possible, ha?

K, thanks everyone. I guess 2 should be good enough a option.

 

[Scooterbug]

You could display the filtered data in a listbox. This way, you could filter the results by PetType via a combo box. Then, based on the selection from the combo box you can set the criteria for the row source for the list box.

edit:
Quick little example attached

 

[someboddy]

No, that's not what I've meant. I've attached an example with my workaround number 2 - a textbox containing the breed name that covers the combobox.

Guess I should have attached it from the beginning...