Trouble filtering combobox

[ittechguy]

I'm trying to filter one combobox based on the values of another combo box.

I have a buildings table and a rooms table. There are many rooms in one building and a room can have the same name but be in a completely different building, and thus, be a completely different room. I have a subform which allows me to select a building name. But when I try to select a room, it shows duplicate values as well as rooms which do not belong to that building.

I've tried the method of creating two combo boxes and adding the building ID to the room combo box and then specifying the building combobox. It doesn't work, it asked me to enter parameter value for [forms]![frmBuildings]![BuildingName]. If I hit cancel and try to select a building, there is nothing in the rooms combobox. I've tried this using unbound combo boxes and it worked just fine. But I need them to be bound to a ID, so that won't work.

I've tried doing this using VBA, but I couldn't get the code right. Your help is appreciated.

[orange]

Cascading combo videos 1 and 2

Use Google and research MSAccess cascading comboboxes
Good luck.

[ittechguy]

I've watched both videos. As well as every video I could find on youtube in regards to cascading comboboxes.

I cannot get it to look into my combo box for a value as it should. It always asks me to enter parameter value. I've tried changing the combobox name, nothing is working.

[June7]

We need to review the setup. You can either list the relevant combobox properties and their settings or provide db - follow instructions at bottom of my post.

[ittechguy]

We need to review the setup. You can either list the relevant combobox properties and their settings or provide db - follow instructions at bottom of my post.

I won't be able to upload the db until I get to work tomorrow morning.

I think I figured out why its not working though. These cascading comboboxes are in a subform which is attached to my mainform. According to this, http://www.pcreview.co.uk/threads/su...query.3225031/, I can't refer to the subform, I have to refer to the control containing the subform.

Their example is this: [Forms]![myParentForm]![NameOfControlThatContainsTheSubform]![cboRoute]

To be honest I have no idea what the control that contains the subform is called. I do know my parent form is frmMainCustomers so I tried [forms]![frmMainCustomers]! which access 2010 automatically gives me all the options for frmMainCustomers but none seems to get me to my combo box.

[ittechguy]

I figured it out!

It is important to select the actual name of the subform which is linked to the mainform. I had forgotten, when I created my subform the name I wanted was already taken so I renamed it to "POC." Even after I renamed the subform to frmBuildings, my main form still saw it as POC.

So for anyone else having this issue, if the combo box is inside of a subform which is linked to a main form, you need the parent form first, then the subform, then the combo box name.

In my case, [Forms]![frmMainCustomers]![POC]![BuildingName]

[June7]

Actually, if both comboboxes are on the same form, even a subform, the second combobox RowSource can be simply like:

SELECT ID, LastName FROM Customers WHERE BuildingID = [combobox1name];

[ittechguy]

Interesting. I could've swore I tried just [Building Name] as the criteria in design view of the query and it didn't work. Will try that again tomorrow and see what I get.

[June7]

That is an SQL statement in RowSource, not a query object.

[ittechguy]

If I go to row source and click on the three dots it opens up a query design page. And i can go to sql view if i want to. Isn't that the same thing?

[June7]

Yes, can do that or simply type out the SQL statement directly in the RowSource.

[ittechguy]

BuildingsSwitches3.zipI'm having problems with my combobox. I thought it was working but after further testing, I found that if I open up frmMainCustomer and select a building name under the subform, I can then select a room name. I only see the rooms which are in that building and everything works great. However, if I go down a line and select a second building (as each customer can own more than one building/room, after I then select a room for that second building, the room for my first building disappears. it does not allow me to store multiple values. What am I doing wrong?

[June7]

Unfortunately, dependent combobox with lookup alias doesn't work nicely in continuous or datasheet view form. Because once the combobox RowSource is filtered, the alias values for other records are not available for display. This is a known condition discussed in many threads and dealing with it is not simple.

An alternative is to not use lookup alias. Since your building name values are so small (just numbers up to 4 digits) could just save the BuildingName value instead of BuildingID. Then the BuildingID field becomes unnecessary.

[ittechguy]

Interesting. Well most buildings are numbers with up to 4 digits but a couple are words up to 10 letters. I had thought about having my buildings table have only one field, make it text and set it to indexed, no duplicates.

Afaik, I need the building ID because I can't just select a building name, I need the ID.

[June7]

No, this can be designed to eliminate the BuildingID. It's just an autonumber field to generate a unique record identifier. Your BuildingName values are unique so the table actually has two unique identifiers. The autonumber is really not needed in this case and eliminating would simplify the situation. If the building name values were long like: 'Alaska Native Science and Engineering Program' then might want to use the autonumber ID. But memory is cheap these days and saving long text identifiers not such a big deal and if your db is not terribly large, indexing on text fields probably not an issue. Another reason to save the ID is to keep the db size down because repeatedly saving long text values can use up Access 2GB limit. But again, if the db remains small, not an issue.

Also, the RoomID in Rooms is not needed if you want save both building and room identifier into the record.

Just have to decide what works best for you. As I said, dealing with lookup alias in dependent combobox on continuous or datasheet form will not be simple.