Continuous Form's Combo Box not Refreshing

Question

I have a cross-reference tblCustomers-Products and a continuous frmCustomerProducts with fields for Customer and Product. The form is bound to a third table.  The Product field is a combo box that lists valid products based on the value in that is entered into the record’s customer field. All works fine except when I click on another record’s Product field: it still displays the prior customer’s product list. (The form’s Refresh button corrects this problem.) I want to be able to click on another record’s Product field downarrow and have it display the Product list for that record’s customer. I’ve tried to requery the field in the On Click event (Me.Refresh and cboProduct.Requery) but it still displays the prior list. Please help me with the correct code and event.  Thanks much.

Answer 1

You have to requery the combo box in the On Current event of the form as well as in the After Update event of the control bound to the customer field.

Answer 2

Your code that controls the RowSource of the Combobox , based on theCustomer field, needs to also reside in the Form_Current event, in order for the Combobox display to be appropriate for the given record.

Linq ;0)>

---

There's always more than one way to skin a cat!

Answer 3

Thanks, that did it.  I did already have a Product combo box requery on the After Update event of the Customer field (for a new record), but I didn't have a requery on the current event of the form.  I'm still not clear on why the Product.requery on the After Update event of the Customer field updates the Product combo box list, but a Product.requery on the Product combo box itself (like On Click) isn't sufficient.  Still much, much to learn...  Thanks again.

Answer 4

Marshall - thanks for the reply.  I just have a form - no subform.  I do have the Where clause you mentioned, so the lookup only displays products for that record's customer. It works great when adding a new record to the continuous form, but when I moved from the Product field of one record to another, the lookup was not getting updated.  Didn't realize I needed a requery on the current event of the form, too.

Thanks.

Answer 5

I think the answer to your question in the situation you currently have is to use the form's Current event to requery the combo box, but that dependes on how the combo box's row source query is filtered.  I think the row source query needs to use a Where clause with a condition like:

   CustomerID = Forms!theform.txtCustomer

I don't understand why you would want to display records for multiple customers like that.  Normally a mainform bound to the customers table and a continuous subform bound to your customer-product table.  The LinkMaster/Child properties limit the subform to a single customer's products.  The main form requries the subform combo box when a different customer is displayed in the mainform.  Think about anyway.