Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Function to reset all fields on a form except the current one using a loop

I need a function that will loop through all of the field controls (text boxes, comboboxes, listboxes) on a form and set them to blank values.  There is no dataset behind this form.  THe only control that should not be reset to a blank value is the control that has the current focus.

Thanks
0
seanmrmd
Asked:
seanmrmd
  • 3
3 Solutions
 
flavoCommented:
If Me.dirty then
 Me.undo
end if

or

Dim ctrl as control
For each ctrl in Me.controls
   ctrl.Value = ""
next


Dave
0
 
flavoCommented:
woops, forgot about the focus bit

Dim ctrl as control
For each ctrl in Me.controls
  If not ctrl.inselection then
     ctrl.Value = ""
  end if
next
0
 
flavoCommented:
revised to take into account lables

Dim ctrl As Control

For Each ctrl In Me.Controls

    If Not ctrl.ControlType = acLabel Then
        ctrl.Value = ""
    End If
Next

0
 
realraelCommented:

here's what you need to do:

1. save the current control (i'm not sure how you'd call clearing the form, so place this code where appropriate):

    Dim LastControl As Control
   
    Set LastControl = Me.ActiveControl
    MsgBox LastControl.Name
   
    ClearForm LastControl

2. Define the ClearForm function:

Private Sub ClearForm(LastControl As Control)
    Dim i As Integer
   
    For i = 0 To Me.Controls.Count - 1
        If i <> LastControl Then
            If TypeOf Me.Controls(i) Is TextBox Then
                Me.Controls(i).Text = ""
            Else
                If TypeOf Me.Controls(i) Is ListBox Or TypeOf Me.Controls(i) Is ComboBox Then
                    Me.Controls(i).Clear
                End If
            End If
        End If
    Next
End Sub

- rael
0
 
willcode4coffeeCommented:
Or you could do this:

Private Sub ClearForm()

    Dim obj As Object
   
    On Error Resume Next
   
    For Each obj In Me
        obj.Clear
        obj.Text = ""
        obj.Value = Nothing
    Next

End Sub


This will loop through all objects on the form and attempt to clear, set the value to "" and set the value to Nothing. Since you set On Error Resume Next, if a method doesn't apply (such as Clear() for a textbox), it just ignores that and goes ahead.

I wouldn't use On Error Resume Next for many things, but this is a very limited sub that does nothing but clear values so I think it is safe and acceptable. It sure beats lots of if...then...else or select case statements.

M@
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now