How refresh combo box when RowSource query depends on another cont

[Guest]

I have an Access 2003 combo box on a form whose RowSource is a query which
depends on the value of another control on the form. The other control is
derived from other values - it is not bound. I can't get the first combo box
to refresh whenever the value of the second control changes as I move from 1
record to another. I've tried requerying the combo box in the form's
OnCurrent event and in the OnChange event of the second control but it
doesn't work.

 

[Marshall Barton]

I have an Access 2003 combo box on a form whose RowSource is a query which
depends on the value of another control on the form. The other control is
derived from other values - it is not bound. I can't get the first combo box
to refresh whenever the value of the second control changes as I move from 1
record to another. I've tried requerying the combo box in the form's
OnCurrent event and in the OnChange event of the second control but it
doesn't work.

You definitely need to requery in the Current event, but
only after the unbound control contains the calculated
value. Since you can not reasonably determine when control
expressions are evaluated, you need to use VBA code in the
Current event to calculate a different, hidden unbound
control's value and use this control in the query's
criteria.

 

[JethroUK©]

try update event of the control that you are changing:

Sub anothercontrol_afterupdate
mycombo.requery
end sub

 

[Guest]

Thanks Jethro

try update event of the control that you are changing:

Sub anothercontrol_afterupdate
mycombo.requery
end sub

 

[Guest]

Thanks Marshall

You definitely need to requery in the Current event, but
only after the unbound control contains the calculated
value. Since you can not reasonably determine when control
expressions are evaluated, you need to use VBA code in the
Current event to calculate a different, hidden unbound
control's value and use this control in the query's
criteria.