keep default in drop down

[qwertyjjj]

A combo box on my form is filled from some VBA SQL, e.g.
combo_DivLevel.RowSource = "SELECT DISTINCT [Div Level] FROM Structure WHERE DivisionID = 3"

I also have the default value of the combo set to: "All"

The idea is that a user can click on all, to either refresh the combo from the start or to select all sub divisions from another combo.
The problem is that this combo's row source is changed when other options on the form are clicked and the default value of "All" then disappears.

Is there a way for the default to always remain or do I have to set this in the VBA code each time I change the row source ?

 

[qwertyjjj]

bump .....

 

[qwertyjjj]

bump .....

 

[KingRudeDog]

Im not sure if I understand your question fully, but it seems that you are looking for the drop down to return to the default value again "All" after the user has made his/her choice? If that's not it, than sorry...

But if it is then the solution is easy.

Open up the properties menu for that combobox and set an event in the "On Lost Focus" field (or if your not opening another form or procedure, you can try the "After Update" field...so on and so on.)

And you said drop down, so Im assuming this is a combox....

when the vb code window opens up,

Private Sub ComboBox(Name)_LostFocus()
ComboBox(Name).Text = "All"
End Sub

And that should be it. So when they make thier choice, the focus goes to the new field and the text default for the ComboBox returns to "All"

Like I said before, if it really isn't losing focus (Like its more of a procedure, or a calculation rather than another field or form) you may have to another tag like "AfterUpdate" instead...

 

[KingRudeDog]

Once again...thats if I understood your question correctly...

 

[qwertyjjj]

Hi
Thanks for the reply.
The problem is that as soon as the user makes a choice, the combo won't display the default again.
I have got round this in the meantime by adding a union in the select query to populate the combo... (eg)

SELECT 'All' FROM TableName UNION SELECT 'ID' FROM TableName