Reset Combo Boxes

[John liem]

I have combo boxes used in a form to make selections criteria of a query and would like to reset all the combo boxes in one click (empty the contents of the combo boxes) using a "Reset" button. Any idea's how to do this?

 

[Fizzio]

Try this.

The two ways I would do this are either via a for...next loop or via each individual combo but for...next is much easier.
This will set each combo in your form to null in turn

On the On_Click event of the reset button

Dim ctl as control
For each ctl in me.controls

select case ctl.ControlType

Case acComboBox
If ctl.tag = "ResetAllowed" Then 'Add this if you have a lot of Combos and you want to reset only those that have a Tag value = ResetAllowed
ctl.value = Null
End If
End Select
Next ctl

 

[John liem]

Hi Fizio,

Thanks for your reply.
The solution you gave to me was more or less the same solution I have behind my reset button_on_click(), but as my solution, your suggested solution is also still showing the old value and not showing a "null" value after the button has been clicked.

 

[Fizzio]

Did you remember to take out the If...Then ststement (if you are resetting all combos. (I've tried this code and it works OK here) The If...then was to fine tune so you could select combos to be reset by setting their Tag Property to ResetAllowed

ie

Dim ctl as control
For each ctl in me.controls

select case ctl.ControlType

Case acComboBox
ctl.value = Null
End Select
Next ctl

This will reset all combos on your form.

 

[John liem]

Hi Fizzio,

I did what you have suggested, but is still not working, the combo box is a subform in a form. Could thid be the reason why it doesn't work?.

 

[Fizzio]

The code assumes that the reset button is on the same form as the combo box(es). If it is not, (ie the reset button is on the mainform) just substitute me. with
me.NameOfSubform (I think)

 

[John liem]

Hi Fizzio,

I did following:
me.[nameof the subform].ctl.value=null but still not doing the reset, though it doesn't give any error messages.

 

[Fizzio]

you replace the me in the for each line as below. This tells Access to look in the subform for the controls, not the main form

Dim ctl As Control
For Each ctl In Me.NameOfSubform.Controls 'As Typed with No Brackets

Select Case ctl.ControlType

Case acComboBox
ctl.Value = Null
End Select
Next ctl

Open the form Form1 to see this in action (very makeshift demo though - A2k)

 

[John liem]

Hi Fizzio,

You are a superstar! ... it works and solved my problem.
Thanks a million,
John.

 

[Fizzio]

No probs, glad we got there in the end!

 

[John liem]

Hi Fizzio,
I still have problems ... as my combo box is constructed from a query and I am using the statement:
me.recordsetclone.findfirst "[Control Source]='" & me![nameofcombobox] & "'"
me.bookmark=me.recordsetclone.bookmark
The value changes is not reflected in the "Control Source".
What do I do to change the value?, thanks in advance

 

[Fizzio]

You are obviously using the combo to find a record. what exactly is [Control Source] as the line you have posted implies that if is a field in the recordset of the form. The code you have supplied will look the value in the combobox (first column) and see if there is a match in the current recordset with that value in the field [Control Source] and if so, go to that record.

It will not make any changes to the data either in the combobox or in the record, so I'm a but confused which value you want changed

 

[John liem]

Hi Fizzio,

Thanks for your quick reply.
As I am using the value of the "Source Control" in my Combo Box as the criteria of my query, I want to "clear" the "Combo Box" (this is what I see on my screen) and reset ("null" value) the Source Control in the background of my Combo Box.

 

[Fizzio]

You seem to be making this very complicated for yourself.
Are you trying to find a record based on the value of the Combobox or filter the form based on the combo. You have given examples of both and seem to be using both methods on your form - pick one of them.
Get back what you are trying to do (not how) and in the meantime I'll try to knock up an example.

 

[John liem]

Hi Fizzio,

As you said, I am making myself complicated and I managed to change my form such that I don't have the Combo Box as a subform. Everything works as it supposed to work!.
Thanks for your support,
John.

 

[Fizzio]

No problem.