Trouble filtering combobox

[ittechguy]

I removed my buildingid field and made the building field the primary key. Then I recreated my relationships and made a bound combobox which queried for the buildingname only.



It does the same thing it's been doing before. Is that because the rooms combobox is also bound to the room ID instead of a room name?

[June7]

Sorry, probably got a little off track with discussion on building table setup. My goof. If the building combobox is not filtered it doesn't really matter whether you use the ID or the name (although using BuildingName means one less join in queries). Room combobox is dependent and also has lookup alias. When it is filtered not all alias values are available for display. This is where you really need to decide whether to eliminate the RoomID and use RoomName instead.

[ittechguy]

I see. Thanks for the help.

Best I can tell, there is no way to eliminate room ID. I need the ID and I need to filter it based on building.

A room in one building can have the same name as a room in a different building, but they're completely different rooms. If I don't filter the combobox then there will be dozens of rooms with the same name to choose from and only one links to the correct building name.

Perhaps I should scrape the idea of using comboboxes in my subform. But then I need another way to allow users to add customers to buildings and rooms. I'm not sure what the alternative would be.

[June7]

But you are already saving both building and room IDs - and actually, saving the RoomID makes saving BuildingID unnecessary because the RoomID alone can identify location. However, saving BuildingName and RoomName also serves to identify location.

If you save the RoomID, saving building identifier is not necessary because the BuildingID can be retrieved in a query that includes the Rooms table - and then BuildingName is retrieved by including Buildings table in the same query. If you instead save BuildingName and RoomName, not only is the location specified, but that is 2 table joins not needed in query. If you're going to save two values, might as well be the two names and make queries less complicated (as long as these names remain relatively short).

If every building has same room numbering, then a conditional list is not needed. Just have a room combobox that lists numbers 1 thru whatever. But if one building has 10 rooms and another has 20, then yes a conditional list is valid but doesn't have to be a lookup with alias.

If you change Rooms table to have a field for BuildingName instead of BuildingID, the RowSource for dependent Room combobox can be:
SELECT RoomName FROM Rooms WHERE BuildingName=[cboBuilding];

And the RowSource for cboBuilding would simply be: SELECT BuildingName FROM Buildings;

I don't know any other method than dependent comboboxes to give users the 'friendly' interface you desire. Dependent combobox with lookup alias works great in a single view form but unfortunately not so with continuous or datasheet.

[ittechguy]

I changed my row source in the room combobox to just the room name and I changed the control to room name instead of room ID. And I specified it to filter by building name combo.

The combo box works great but it's creating duplicate entries in my room name table. What am I doing wrong?

[ittechguy]

I did some more troubleshooting. I found that duplicate values are only showing in my room name combo box when I select the room under an additional customer.

That is, my form works great until I go to another customer. Then if I select the same room that customer 1 owns, it duplicates the room name. I did not have this problem when I was using the room id

[June7]

I am beginning to wonder what the subforms are for. The main form is bound to Customer so I concluded the CommRooms subform is to enter a record into CustomersToRooms. Is this correct? CustomersToRooms is in the subform RecordSource and really don't understand why you have those other tables in there. If the subform is for data entry to CustomersToRooms - then in the original db, Room combobox is bound to wrong field, should have been: CustomersToRooms.RoomsID
And Building combobox should be UNBOUND.

Now I notice there are comboboxes bound to fields from RoomName table. Usually a form can do data entry/edit for only one table. Yet you have 4 comboboxes bound to fields from 2 tables, none of which are from CustomersToRooms. Makes no sense for this data structure.

This means I misunderstood the original form setup. Because I saw the building combobox I assumed the value was saved into CustomersToRooms. Now I see what I said earlier ("already saving both building and room IDs") is not the case.

None of the data is saved into CustomersToRooms - data in the OTHER tables is being edited by the selections in comboboxes - so what is purpose of this subform?

[ittechguy]

I'm sorry I'm relatively new to databases and might be doing things all wrong.

Yes, I'm trying to get the subform to edit my customers to rooms table. I am trying to create a way to search for a customer and see the rooms that they are the point of contact for. Also I want to be able to make changes in my subform. That is, I should be able to search for customer bob and see he's assigned to building 1, room 1. Then I should be able to also assign him to building 2, room 2. I should also be able to remove a building or room if he is no longer the POC for that room.

I cleaned up my tables a little bit. I removed the roomsname table and put all the information in my rooms table, that seemed to make more sense. I never thought about having an unbound building combo box. I'm going to give that a try!

[June7]

Working with the original db:

1. set the subform RecordSource to: CustomersToRooms - not a query, just the table directly

2. Building combobox must be UNBOUND (and by not having Building table in the RecordSource, this is assured)

3. Room combobox bound to RoomID field from CustomersToRooms - again by not including other tables in RecordSource there will be no confusion

4. leave the subform container MasterChild Links properties as already set - this assures that only related records are displayed

5. SecurityMethod and CabinetKey have no place on form for data entry/edit to CustomersToRooms

6. set up Room combobox as dependent on Building combobox - this will have lookup with alias and the issue already described

Now if you want to switch to saving BuildingName and RoomName into CustomersToRooms, similar steps apply, although now the Building combobox will be bound to field from CustomersToRooms.

I notice you have a related thread https://www.accessforums.net/access/...tml#post291584

Have you looked at the MS Access Desktop Asset Tracking database template?

[ittechguy]

Thank you for the help.

I've done as you've said, and it does work. But I have the same problem when I try to ender a new row of data.

I've done some more research on this. I am thinking about setting up the subform to display the rooms that a customer is already associated to, and have a button to open up a popup form and allow users to add a room or delete a room.

I do not completely understand how this is done. I'm pretty sure the popup form needs to be linked to the subform.

I could create a button to open up the form using vba but I'm not sure how it would be linked.

Also, yes. I've looked at the asset tracking template. It's much simpler than I need but it has given me some very good ideas.

[ittechguy]

Oh and I did try storing building id and to I'd in my customers to rooms table. I had three fields. Customer id, building, id, and room id.

I ran into problems whenever I tried to add a room to a building. It didn't allow me to simply add a room to a building, it said a relational value is required for customer id. That's why I had to separate it.

[June7]

Sorry, don't really understand what you are asking for. Post 26 makes no sense to me.

What do you mean by 'add a room to a building'? Aren't all building and room combinations already in the Rooms table?

[ittechguy]

Maybe I misunderstood you, idk.

I tried having a junction table which contained customer id, building id, and room id. This didn't work because if I tried to add a room inside a building, it would require something to be inserted into the customerid field. Thus, I needed to separate building/room from customers.