Solved

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

Posted on 2004-08-22
7
327 Views
Last Modified: 2008-03-06
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
Comment
Question by:seanmrmd
  • 3
7 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 11867455
If Me.dirty then
 Me.undo
end if

or

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


Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 11867459
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
 
LVL 34

Accepted Solution

by:
flavo earned 168 total points
ID: 11867492
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
 
LVL 3

Assisted Solution

by:realrael
realrael earned 166 total points
ID: 11867520

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
 
LVL 4

Assisted Solution

by:willcode4coffee
willcode4coffee earned 166 total points
ID: 12184482
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question