Split form combobox requery based on row selected

[krausr79]

I have a split form that was not made by wizard. On the form part I have a combobox that is unbound to the form data set. The combobox has a query row source that is based on the current row selected. I want the combobox to have an up-to-date result based on which row is selected.

If I set the combobox to requery in the form_current event then I get what I want. I don't want to put up with the little delay that is generated every time a user changes rows because of the requery, though. I only want the requery to happen when they use the drop-down menu.

I have the requery in the gotfocus event of the combobox on the form. I mostly get what I want this way, however if they select an item in the drop-down list, then choose a new record in the datasheet, then try to use the combobox again, the combobox is not refreshed (because it never lost focus?).

To get around this, I've tried to setfocus to something on the form in the on_current event, but access gives me an error: 'Access can't move focus to the control btn_Refresh' and I'm not sure why.

How can I get the combo to requery only when users are about to use it?

 

[highandwild]

You may have to put up with a short period of processing time whilst the list is refreshed. I cannot see any way around this.

You can requery the combobox using the On Got Focus event of the combobox.

 

[Roku]

Does the logic of combo box selection have any effect on the current record? If the user has explicitly selected a new record outside of the combo box (e.g. by navigation buttons), then the combo must have lost focus.
You could try saving the value of interest from the current record in a variable (in the Form_Current event), whose value is added to the query in the combo gotfocus event. Try tracking this with debug.print statements at the critical points, to make sure you have the values you expect.
In principle I don't see a problem with the logic you describe. I doubt that focus is of any relevance, so I'd be inclined to look for some other cause.

 

[krausr79]

Not so, Roku! I put a messagebox in the gotfocus for the combobox to test it. On first entry, or when changing the focus on the form portion away and back then the messagebox appears. If I am in the combobox and go selecting around in the datasheet portion and then click back into the combobox, the messagebox does not appear. Mysterious!

Edit: also, the combobox does not affect the record selection.

I will try storing a form-global boolean that is set to true oncurrent so we know when it's time to requery.. But what would the trigger be if the gotfocus is not firing??

 

[Roku]

Hmmm - tricky one, this

It sounds to me as if the GotFocus event is not firing for some obscure reason, even though the focus must have moved away if you do those other things as you describe.

Can you explain (or post) the code from the relevant event of the combobox, so see if that provides a clue? Which specific event(s) do you use? OnClick, OnChange, AfterUpdate ...?