Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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?

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788547' date='12-Aug-2009 01:25']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?[/quote]

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788549' date='12-Aug-2009 01:35']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.[/quote]

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  7. #7
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788552' date='12-Aug-2009 01:44']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.[/quote]

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you still have problems you could post a stripped down and zipped copy of your database (remove or alter sensitive data).

  9. #9
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788649' date='12-Aug-2009 12:50']If you still have problems you could post a stripped down and zipped copy of your database (remove or alter sensitive data).[/quote]

    Hans,

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

    Thanks,

    Tina
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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?

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    And shouldn't tblRentalOrderDetails contain a RentalID to indicate the item from Inventory that was rented, rather than a RentalType?
    Regards
    John



  12. #12
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='788753' date='13-Aug-2009 02:34']And shouldn't tblRentalOrderDetails contain a RentalID to indicate the item from Inventory that was rented, rather than a RentalType?[/quote]

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  14. #14
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788862' date='13-Aug-2009 14:49']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.[/quote]

    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

Page 1 of 2 12 LastLast

Posting Permissions

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