Go Premium for a chance to win a PS4. Enter to Win

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
?
336 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 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

885 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