Combo box on subform retains values from previous records

[virtualgreek]

Dear All,

First of all I would like to take the time to thank you all for your efforts and time spent at this wonderful forum. I have found it very helpful with numerous examples available for every level of user.

I am facing a rather weird behaviour with a combo box on a subform (Continuous form).
I have two tables. Order and Order_Details. The master form is based on the Orders tables and the details form (subform) is based on the Order_Details table.

On the main form, I have a field called Country. Each order record is placed for a country. Each country has a list of products available, with different prices. In other words, for Country A, Product A can cost 20 Euro, while in Country B Product A can cost 19.5 Euro. Also, in Country A Product B is available while in Country B Product B is not available.

Based on this information, as soon as the user enters the required data in the main form, I force them to save the record in order to display the continuous form, thus in case the user cancels while entering Order_Details, I avoid the scenario of having orphan Order_Detail records.

On the subform, the user then has to select a product from a combo box. I load the combo box with the list of products available for the country specified on the main form. All is fine when creating the record.

Whilst in edit mode, the user views Order 1 with its respective details, then clicks on the navigation button at the bottom of the form, and jumps to view Order 2 etc etc.

If the user clicks on the Product combo box any of the Order_Detail records from Order 1, this will load the combo box with the list of available products for the country on the main form. So far so good. However, if the user then clicks on the navigation button and jumps to Order 2 and then again clicks on the Product combo box, the weird scenario occurs. The combo box still retains the values of products that were loaded for Order 1. It does not refresh the list.

Essentially it loads the list on the first Order records that I attempt to edit and it retains it until I close the form and re-open it.

I have tried to reload using the Me.Requery command on the Got focus event but it doesnt seem to be executed. Same thing for the On click event.

Any ideas what causes this behaviour and mostly, how can I solve it ?

Kind Regards
Virtualgreek

[MSeda]

Try in the main forms onCurrent event
me.DetailSub.Form.ProductCombo.Requery

(of course change the object names to match the ones on your form)

This will specifically requery the combo box when the user selects another order on the main form.

[virtualgreek]

Try in the main forms onCurrent event
me.DetailSub.Form.ProductCombo.Requery

(of course change the object names to match the ones on your form)

This will specifically requery the combo box when the user selects another order on the main form.

MSeda, thanks for the reply.

I have tried the following on the main form (Orders), current event.

Me.frmOrderDetails.Form.cboProduct.requery

but it didnt work. Apparently it cannot reference the subform at that point or the syntax is incorrect.

On the other hand I have tried the command Me.frmOrderDetails.Form.Requery seems to work. (at the same event). But I havent tested it for any 'side effects'.

If I requery the whole subform, do you anticipate and performace problems ?

Kind Regards
Virtualgreek

[MSeda]

I'm not sure why the combo won't requery the syntax looks correct assuming the form and combo box names are spelled correctly. I don't see any thing in your description that would indicate that this might not work.

Apparently it cannot reference the subform at that point or the syntax is incorrect.

Do you get an error message telling you this or the combo just doesn't update?

It would be preferable to only requery the combo box since the form has updated via the link fields. Requerying the entire subform isn't a terrible thing, but it will take slightly longer than requerying the combo only.

[virtualgreek]

Mseda, thanks again for your reply.

I will check the names and try it again.

In the meantime my temp solution has worked, and it doesnt seem to cause a problem regarding performace, since an Order doesnt really have that many Order_Detail records.

Thanks again.
Virtualgreek