clearing linked combo box

[Guest]

Hi,

I've got 2 combo boxes on a form, the 2nd one is filled based on a selection
from the first combo box, triggering off a Change event.

If I select a name and then clear it from the 1st combo box the options
available for that choice remain under the 2nd combo box, how do I set it so
that if nothing is displayed in the 1st combo box the 2nd combo box is empty
(no options available)?

Thanks

 

[Guest]

Requery Combo2 whenever there is a change in Combo1

 

[Marshall Barton]

I've got 2 combo boxes on a form, the 2nd one is filled based on a selection
from the first combo box, triggering off a Change event.

If I select a name and then clear it from the 1st combo box the options
available for that choice remain under the 2nd combo box, how do I set it so
that if nothing is displayed in the 1st combo box the 2nd combo box is empty
(no options available)?

Don't use the Change event for this kind of thing, it fires
on every keystroke in the control.

Use the first combo box's AfterUpdate event to manage the
second combo box:

Me.secondcombo = Null 'clear old value
Me.secondcombo. Requery 'sync rowsource query

 

[Guest]

is it possible to shut off keyboard use on a combo box, so that only values
from the combo list can be chosen with the mouse and that even deleting or
backspacing a highlighted name in the combo box doesn't work?

This is the code I'm currently using on the Change event for the 1st combo:
Dim strSQL As String
Me!Event_Type_Combo.Value = Null

strSQL = "SELECT Function_Name"
strSQL = strSQL & " FROM [Event Table]"
strSQL = strSQL & " WHERE Department_ID=" & Me!Employee_Name_Combo.Column(0)
& ";"
Me!Event_Type_Combo.RowSource = strSQL

The 2nd combo has no rowsource set under properties, but it's row source
type is set to table/query

Thanks

 

[Marshall Barton]

is it possible to shut off keyboard use on a combo box, so that only values
from the combo list can be chosen with the mouse and that even deleting or
backspacing a highlighted name in the combo box doesn't work?

This is the code I'm currently using on the Change event for the 1st combo:
Dim strSQL As String
Me!Event_Type_Combo.Value = Null

strSQL = "SELECT Function_Name"
strSQL = strSQL & " FROM [Event Table]"
strSQL = strSQL & " WHERE Department_ID=" & Me!Employee_Name_Combo.Column(0)
& ";"
Me!Event_Type_Combo.RowSource = strSQL

The 2nd combo has no rowsource set under properties, but it's row source
type is set to table/query

I thought I told you to use the AfterUpdate event instead of
the Change event. The Change event is **not** appropriate
for this purpose.

You can intercept the keystrokes by using one of the Key ...
events, but I think you should let users use the keyboard.
Set the AutoExpand property to Yes for the quick search
feature and set the LimitToList to Yes to prevent invalid
entries.

The SQL statement shoul include an ORDER BY clause to sort
the list, making it easier to search through.

 

[Guest]

To get around this easily I did the following:

Limit the 1st combo to only values in the list
Initially unEnabled the 2nd combo
Added code on AfterUpdate to the 1st combo to Enable the 2nd combo when the
1st combo has a value and fill the 2nd combo with the relevant options, and
disenable it if the 1st combo has a blank value ("")