requery problem in combobox!?

[rich_bell20]

hey, i have a datasheet where products are selected from each combobox based on a category selected from another combobox.
If a product from building is selected, it can be added to the "cart" then if i selected category "bathrooms" i have to requery it to show the avalable records, but it wipes away the building one!, is there anyway i can keep them all on, cos i will be selecting a number of products from different categories. please download the database i have provided cos its kinda difficult to explain, thanks alot everyone!

 

[dcx693]

I applaud you for trying to get this information into a database, but the file you uploaded needs some serious work. I just spent like 20 minutes making changes to it to try and get it to work.

Without getting too in depth, I can suggest a few things:
1- in order to add an item to the subform you have in the frmInvoice, you need to send a product ID to it. Your database is sending a categoryID
2- your subform recordsource has products and order details, but not your orders table. You need the orders and the order detail table, but only the product table to pull information from.
3- The code to "add" the product to the cart should be something like: Me![Order Details Extended]![ProductID]=Me.combo47
4- I'd suggest moving the code to update the subform out of the AfterUpdate event of the combo box since it's easy for users to accidentally make a combo box selection. I would place a command button labeled "Add to cart" or something similar.
5- The productID field in the subform doesn't need to be a combo box, since users are already making choices of products from the combo boxes on the main form.

These are just a few suggestions. You're on the right track overall, but a lot of little things need to be tweaked.