Suggestions on on alternative to on current code?

[gojets1721]

I have a combo box (CB2) in my form whose items are dependent on another combo box (CB1).

In order to make sure CB2 is always showing the correct items, I have code in the on current event of the form which requeries CB2 as the user changes records.

However, this on current code seems to be causing my form to 'flicker' when changing records (a common issue when looking this problem up online; when I get rid of this code, the flicker goes away too).

Any suggestions on how to have the CB2 update without having to use the on current event (if that's even possible)?

Thanks!

 

[LarryE]

Use the CB1 After Update event to recalc CB2 instead of the Form On Current event. Put
Me.CB2.Recalc
in the CB1 After Update event.

 

[mike60smart]

You would normally make reference to the CB1 in the Row Source of CB2 using a Forms Reference

 

[gojets1721]

You would normally make reference to the CB1 in the Row Source of CB2 using a Forms Reference

How would you suggest coding that?

CB1 is called cbCategory and CB2 is called cbSubCategory

 

[mike60smart]

How would you suggest coding that?

CB1 is called cbCategory and CB2 is called cbSubCategory

In Cb2 in the criteria for the CategoryID field you would use:-
[Forms]![Name of Form Control]![CategoryID]

If the Form is is Subform then use something like this:-
In Cb2 in the criteria for the CategoryID field you would use:-
[Forms]![Name of MainForm Control]![Name of SubForm Control]![CategoryID]

 

[gojets1721]

In Cb2 in the criteria for the CategoryID field you would use:-
[Forms]![Name of Form Control]![CategoryID]

If the Form is is Subform then use something like this:-
In Cb2 in the criteria for the CategoryID field you would use:-
[Forms]![Name of MainForm Control]![Name of SubForm Control]![CategoryID]

So I have that currently. The issue is that CB2 doesn't update when moving from record to record. I currently achieve that by requerying it in the on current event

 

[mike60smart]

So I have that currently. The issue is that CB2 doesn't update when moving from record to record. I currently achieve that by requerying it in the on current event

Can you upload a copy of the Db?

 

[mike60smart]

So I have that currently. The issue is that CB2 doesn't update when moving from record to record. I currently achieve that by requerying it in the on current event

Not following. The cascade works on the record that you are currently entering.

 

[MajP]

Use the CB1 After Update event to recalc CB2 instead of the Form On Current event. Put
Me.CB2.Recalc
in the CB1 After Update event.

No you still have to use the On Current event to requery the combo. Imagine you are in single form view and the first record is filtered to a category. Lets say "Category1". Then you go to the next record and filter on "Category2". Now go back to record 1. It will have to requery the combo or you will not see the value since the combo would be filtered to Category 2, but the choice is in Category1.

 

[bastanu]

@gojets1721: Is this a continuous or datasheet form? If you simply want to requery the list for combo 2 (meaning the available subcategories for the selected category in combo 1) the best place to do that is the Enter or GotFocus events of combo 2 itself. So when you enter the combo it requeries itself: Me.cboSubCategory.Requery

If your problem is that the subcategory combo is not populated in the records not matching the category of the first record (what would happen for the continuous or datasheet forms) then usually my solution is to load the combo 2 unfiltered (so not be based on the category combo) so it works for all records, then in the Enter event change its row source to a filtered SQL based on combo1 so only the appropriate subcategories are available for picking and finally change it back to the original unfiltered row source on Exit (or use the GotFocus\LostFOcus events).

To deal with the flicker you could also try to set the Echo False (Docmd.Echo False) just before you do the requery then set it back to True after.

Cheers,

 

[CJ_London]

Situation not very well described- I presume the rowsource of cb2 has criteria based on cb1 - sql something like

select id, name from sometable where type =[cb1]

if so you are going about this the wrong way.
1. Set the rowsource without the where clause - then no need to requery on current
2. In the cb2 enter or got focus event apply the criteria to the rowsource. No need to requery since changing the rowsource will do that automatically
3. In the cb2 exit or lost focus event, remove the criteria again

See this link for an example

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

 

[gojets1721]

Situation not very well described- I presume the rowsource of cb2 has criteria based on cb1 - sql something like

select id, name from sometable where type =[cb1]

if so you are going about this the wrong way.
1. Set the rowsource without the where clause - then no need to requery on current
2. In the cb2 enter or got focus event apply the criteria to the rowsource. No need to requery since changing the rowsource will do that automatically
3. In the cb2 exit or lost focus event, remove the criteria again

See this link for an example

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

Thank you so much; this was exactly what I was looking for. The example DB was super helpful. I ended up going with technique 1 and it worked like a charm. Thanks again