Reset combo box on unbound form

[Opal]

This one has me stumped. I am running Access 2003 and have
an unbound form with 10 combo boxes that I need to reset to null
after a record is recorded in the table.
The user selects a combo box choosing either "Yes", "No" or
"N/A". If the user selects "No" another combo box comes up
(one of the 10) to make a detailed choice in his response to the
question posed. After this record is submitted, the form clears
and the user will enter another record. He may answer "No"
to a different question and a different combo box (one ot the
10) will come up with a list of choices as his response. He
would then hit submit. The problem is, the table records
for the second (and subsequent) records the choice for the
1st record where the answer was "No" plus the choice for the
second where the answer was "No" and so on. I want to be able
to reset the value of these combo boxes to null as I do not
want the value from a previous recorded on the next. I have
tried re-query on the form's current event and .value = null
and nothing works. Could someone please point me in
the right direction?? Thank you.

 

[Jeanette Cunningham]

Hi Opal,
try a different type of set up. Use a bound form that will write the users
selections directly to a table without worrying about unbound combos and
recordsets.

A bound form with bound combos will record straight to the table, and even
better, when you move to the next new record, access will automatically
reset the combos to empty, ready for the user to enter the data for the new
record.

So a bound form will make this very easy to do and greatly reduce the amount
of code you need to write.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Opal]

Hi Opal,
try a different type of set up. Use a bound form that will write the users
selections directly to a  table without worrying about unbound combos and
recordsets.

A bound form with bound combos will record straight to the table, and even
better, when you move to the next new record, access will automatically
reset the combos to empty, ready for the user to enter the data for the new
record.

So a bound form will make this very easy to do and greatly reduce the amount
of code you need to write.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia






- Show quoted text -

Yes, I know that a bound form would make my life simpler, but,
due to other controls on the form and the way the database
was set up (by someone) else, my only option is an unbound
form. Any other suggestions?

 

[Jeanette Cunningham]

Sorry I can't help further, but I have never gone to all that much trouble
just to save data in a table. Maybe someone with experience using unbound
forms for complicated data entry will jump in and offer a suggestion.
<anyone>?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Opal,
try a different type of set up. Use a bound form that will write the users
selections directly to a table without worrying about unbound combos and
recordsets.

A bound form with bound combos will record straight to the table, and even
better, when you move to the next new record, access will automatically
reset the combos to empty, ready for the user to enter the data for the
new
record.

So a bound form will make this very easy to do and greatly reduce the
amount
of code you need to write.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia






- Show quoted text -

Yes, I know that a bound form would make my life simpler, but,
due to other controls on the form and the way the database
was set up (by someone) else, my only option is an unbound
form. Any other suggestions?

 

[Piet Linden]

This one has me stumped.  I am running Access 2003 and have
an unbound form with 10 combo boxes that I need to reset to null
after a record is recorded in the table.

Private Sub cmdClearCombos_Click()
' Resets ALL combobox values in the current form to null.

Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is ComboBox Then
ctl.Value = Null
End If
Next

End Sub

 

[Opal]

Private Sub cmdClearCombos_Click()
' Resets ALL combobox values in the current form to null.

    Dim ctl As Control

    For Each ctl In Me.Controls
        If TypeOf ctl Is ComboBox Then
            ctl.Value = Null
        End If
    Next

End Sub

Thank you Piet... I will give it a try tomorrow at work.