Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-22
7
Medium Priority
?
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 672 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 664 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 664 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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