Reset Drop Down on Form 1

[accessewbie]

I have several drop down menus on a form. However, when a user goes to enter a new record the previous entries on the drop down menus are defaulting. I want the drop downs to reset to a blank value for each new record. Any ideas? From reading somewhat similar posts I have a feeling it will require some coding - I am a newbie! Thanks in advance!!!!!

 

[VBslammer]

You can set them to Null in the Form_Current() event:

Private Sub Form_Current()
  Me!cboMenu1.Value = Null
  Me!cboMenu2.Value = Null
  Me!cboMenu3.Value = Null
End Sub

If you have many controls to reset, you can loop through them and check their type, then use a naming scheme to identify the specific controls:

Private Sub Form_Current()
  Dim ctl As Control
  For Each ctl In Me.Controls
    If TypeOf ctl Is ComboBox Then
      If Left(ctl.Name, 7) = "cboMenu" Then
        ctl.Value = Null
      End If
    End If
  Next ctl
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[accessewbie]

I apologize for my lack of knowledge on this but for example I have a drop down menu named Auditor...I tried replacing cboMenu1 with the named field but I get an error. I am not doing this right I assume. Can you help once again? Thanks!


Private Sub Form_Current()
Me!cboMenu1.Value = Null
Me!cboMenu2.Value = Null
Me!cboMenu3.Value = Null
End Sub

 

[grande]

VBSlammer,
Man, I didn't even post this and you helped me out huge!

Thanks, I didn't know you could do "For Each ctl IN Me.Controls" or anything like that!

-------------------------
Just call me Captain Awesome.

 

[VBslammer]

Are you talking about Comboboxes? I know Excel uses the term "DropDown" for combo boxes, so I'm guessing you're using comboboxes for selecting specific records on a form?

My experience with controls is that it's better to name them differently than the field they are bound to. For example, if the field name is "Auditor" then the combobox would be named "cboAuditor" and you could refer to it like this:
[tt]
Me!cboAuditor.Value = Null[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[accessewbie]

I see it is a list box - not a drop down as I had originally thought. The drop down does reset itself but the list box remains from the previous entry.

 

[RoyVidar]

If the list control has the multiselect property set to something other than "none", you may have to do something like this:

[tt]me!lstListBox.rowsource=me!lstListBox[/tt]

some similar techniques as demonstrated above, can also be found in faq702-5010, end of first section.

Roy-Vidar

 

[VBslammer]

[tt]Me!lstAuditor.ListIndex = -1[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[RoyVidar]

Oups, missed something at the end of the reference

[tt]me!lstListBox.rowsource=me!lstListBox.rovsource[/tt]

Roy-Vidar

 

[RoyVidar]

Oups, missed something at the end of the reference

[tt]me!lstListBox.rowsource=me!lstListBox.rowsource[/tt]

Roy-Vidar