Filtering based on selection of group in combo box

Topic

New Reply

I have an Event Management database I am building. I have a form where we match the Customer to the rentals (we select rentals from the drop down) and it fills in the other fields: Description, Price, etc. We have about 100 rentals so far in our Rental table that is connected to the TblRentalServiceOrders and its orders: TblRentalOrdersDetails. I have made a form and subform based on these tables and queries (where the calculations are). Ok, so my question: in my subform: FrmRentalOrderdetailsSubform I have the drop down field: Type of Rental that has the type of rental: Rental Displays, Modular Furniture, Rental Furniture, Accessories, etc. When the user clicks the drop down and selects a type of rental like Rental Displays I want my drop down to only show all of the matching fields: Description, Comments, Price, etc. I guess to filter out only what is selected. I am also thinking of if the user makes a mistake (as they always do) and selects the wrong item to be able to undo the filter and select a different one. I hope this makes sense. This is just because the info in the Rentals table is growing.

Thank you so much in advance for any assistance you may be able to provide.

Tina

Reply | Quote

Replies

I don’t understand what you mean by “When the user clicks the drop down and selects a type of rental like Rental Displays I want my drop down to only show all of the matching fields”. Is the second “drop down” the same as the first one, or a different one?

Reply | Quote

I don’t understand what you mean by “When the user clicks the drop down and selects a type of rental like Rental Displays I want my drop down to only show all of the matching fields”. Is the second “drop down” the same as the first one, or a different one?

I made it sound more complicated that it is. There is one drop down called Rental Types that show values such as: Rental Displays etc. When the user selects a value in this first drop down the corresponding fields beside it fill in because they are all from the same table: You know like an order form with products to choose from where the user would select a product list drop down list in the subform. Does that help?

Thanks so much,

Tina

Reply | Quote

You could add the table that contains the details of the rental types to the query that acts as record source of the subform. If the user selects a different rental type from the dropdown, the corresponding details would automatically be filled in.

Reply | Quote

You could add the table that contains the details of the rental types to the query that acts as record source of the subform. If the user selects a different rental type from the dropdown, the corresponding details would automatically be filled in.

I am sorry that I was not clear about what I wanted. That table is already added to the query that acts as a record source for the subform. The info already automatically fills in. What I want the drop down to do is to filter the rental types out when the user selects a type from the drop down. So if the user selects “Modular Furniture” from the drop down, then the drop down would only show all the Modular Furniture info and then…………..Ok, I feel stupid because now I see what would happen: the user would select Modular Furniture the first time and the drop down could filter all the Modular Furniture info but what happens when the user clicks the drop down again to select the actual value that they want to be filled in, the drop down is acting as a filter. Perhaps I could have a drop down above the could be connected to my drop down field: Rental Types so when the user selects a Rental Type from the first drop down it would filter the Rental Types drop down to only show those particular rental types selected and then the user could effectively select a proper value to be populated. Make sense?

I am sorry for this confusion. It is late and I am tired.

Thanks so much,

Tina

Reply | Quote

You can limit (filter) the choices in a second combo box when the user selects an item in a first combo box. See How to synchronize two combo boxes on a form in Access 2002 or in Access 2003. There are also several examples of cascading combo boxes in this forum, e.g. [post=”132255″]post 132255[/post].

Reply | Quote

You can limit (filter) the choices in a second combo box when the user selects an item in a first combo box. See How to synchronize two combo boxes on a form in Access 2002 or in Access 2003. There are also several examples of cascading combo boxes in this forum, e.g. [post=”132255″]post 132255[/post].

Hans,

I tried the first method to synchronize two combo boxes, but it does not work in my case because I am dealing with one bound and one unbound combo box. I will look at the second method you posted. I am sure that there is a simple way to do what I want and if you saw what I was trying to do, you would think it is easier than I make it out to be.

Thanks,

Tina

Reply | Quote

If you still have problems you could post a stripped down and zipped copy of your database (remove or alter sensitive data).

Reply | Quote

If you still have problems you could post a stripped down and zipped copy of your database (remove or alter sensitive data).

Hans,

Here is the database. If you can help, I would so greatly appreciate it ever so much.

Thanks,

Tina

Reply | Quote

I’m confused about your setup. Shouldn’t the user select a rental type in the main form, and then only be able to select items corresponding to that rental type in the subform?

Reply | Quote

And shouldn’t tblRentalOrderDetails contain a RentalID to indicate the item from Inventory that was rented, rather than a RentalType?

Reply | Quote

And shouldn’t tblRentalOrderDetails contain a RentalID to indicate the item from Inventory that was rented, rather than a RentalType?

To be clear:
There is a TblInventoryofRentals that contains all the rental inventory
TblRentalOrders is the top part of the “invoice” that shows the customer info
TblRentalOrderdetails is the bottom part of the “invoice” that shows the rentals that the customer orders. There are 3 forms that correspond to the tables. In the FrmRentalOrderdetailsSubform I do have a matching RentalInfoID, I just don’t have it visible because I don’t need to see it. This is the child/parent link connecting the FrmRentalOrders and the FrmRentalOrderdetailsSubform.

So, the TblRentalInventoryofRentals has a RentalID and the other two contain the Primary ID RentalOrderID that connect them.

What do you see is wrong? I would really appreciate your feedback here.

Thanks,

Tina

Reply | Quote

I don’t understand why you have a Type of Rental dropdown on the main form and on the subform, and what their relationship is supposed to be.

Reply | Quote

I don’t understand why you have a Type of Rental dropdown on the main form and on the subform, and what their relationship is supposed to be.

Hans,

The Type of Rental on the main form was just a drop down cascade or filter, if you will that I was fooling around with that would filter out things from the Type of Rental on the subform. Gosh, I am so sorry for this great confusion. I was looking at my structure and decided to take the Type of Rental out of the TblInventoryofRentals and put it into its own table called TblRentalCategories and then link it to the TblInventoryofRentals just like a Product and Category relationship.

Anyhow, please, please ignore the drop down on the main form, I was just trying to accomplish what I am asking you to help with by creating a combo box.

Sorry,

Tina

Reply | Quote

You would need to split your TblInventoryOfRentals into 3 tables:

1) A table tblRentalTypes that lists the main types of rental: Modular Displays, Modular Furniture etc.
2) A table tblRentalCategories that lists the article categories within each rental type, corresponding to the Description field.
For example for Rental Furniture: Bar Stools, Coffee Tables, etc.
3) The TblInventoryOfRentals table, where the main text field now becomes the Comments field (RentalID remains the primary key)

Correspondingly, you need 3 combo boxes.
1) In the first (unbound) combo box, the user selects a rental type. In the After Update event of this combo box, the row source of the second combo box is updated.
2) In the second (also unbound) combo box, the user selects a category. In the After Update event of this combo box, the row source of the third combo box is updated.
3) A third combo box bound to the TypeOfRental field in which the user selects an article based on the Comments field.

To make this work in a continuous form is tricky, you need to place transparent text boxes on top of the first two combo boxes. You might prefer to leave things as they are.

Reply | Quote

And shouldn’t tblRentalOrderDetails contain a RentalID to indicate the item from Inventory that was rented, rather than a RentalType?

I see now that it does contain what I was referring to, but it looks like it does not.

The field TypeOfRental in tblRentalOrderDetails displays the text as shown above, but is actually a hidden number.
The combination of hiding the number, and using a field name that suggested it was a Rental Type confused me.

For what it is worth, I don’t think it is a good idea to hide things in tables. By all means do it on forms, but I think if you look at tables you should see what is really being stored.

I attach a modified version which tries to do what you originally asked. There is a problem. When you use the combo box on the main form to filter the selections in the subform, the change applies to all rows in the subform, not just new ones. So in any rows where you have already entered data, and the the type is different from the filter you have just applied, the combo goes blank.

Reply | Quote

I see now that it does contain what I was referring to, but it looks like it does not.

The field TypeOfRental in tblRentalOrderDetails displays the text as shown above, but is actually a hidden number.
The combination of hiding the number, and using a field name that suggested it was a Rental Type confused me.

For what it is worth, I don’t think it is a good idea to hide things in tables. By all means do it on forms, but I think if you look at tables you should see what is really being stored.

I attach a modified version which tries to do what you originally asked. There is a problem. When you use the combo box on the main form to filter the selections in the subform, the change applies to all rows in the subform, not just new ones. So in any rows where you have already entered data, and the the type is different from the filter you have just applied, the combo goes blank.

I have to say I am relieved that I put together incorrect tables. I was concerned and up late trying to see what I did wrong. The “filter” combo box is exactly what I wanted. It does not matter that the combo goes blank, as long as it stores the values in the table.

Thanks so much,

Tina

Reply | Quote