Results 1 to 5 of 5
  1. #1
    KPAW is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7

    Clear active text box with using VBA.

    I have a form that is an unbound form which I wish to use for data entry. The form consists of several text boxes each with its own name. The text boxes are use to input currency and integer data.

    However, I want to be able to delete the data from any one of the text boxes by clicking a button, and have data in the other text boxes remain while the data in the text box that has the focus should be cleared.

    I have the following code


    Private Sub CmdBtnClrSelection_Click()


    Dim Ctl As Control

    For Each Ctl In Me.Controls

    If Ctl.ControlType = acTextBox Then

    If Ctl.Value <> True Then


    Ctl.Value = False
    Else

    If Ctl.Value <> False Then

    Screen.ActiveControl = ""

    End If
    End If
    End If
    Next Ctl
    End Sub

    However, when I run the code it clears the text box that has the focus and places a zero in all text boxes including the one that was cleared of the original value.

    I am struggling to understand why this is happening and to correct same but I am not getting through, hence I am seeking assistance.

    Thanks in advance for any assistance.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Your code:
    Code:
    Private Sub CmdBtnClrSelection_Click()
        Dim Ctl As Control
        
        For Each Ctl In Me.Controls   '<<-- Loops through EVERY control on the form
            If Ctl.ControlType = acTextBox Then    '<<-- checks to see if control is a text box
                If Ctl.Value <> True Then    '<<-- is the value of the control Not True? (doesn't really make sense)
                    Ctl.Value = False           '<<-- set the control value to FALSE (ie 0)
                Else
                    If Ctl.Value <> False Then    '<<-- is the value of the control Not False? (doesn't really make sense
                        Screen.ActiveControl = ""           '<<-- set the control value to an empty string
                    End If
                End If
            End If
        Next Ctl
    End Sub
    Once you click the button, the button becomes the ActiveControl and you don't know what the previous control was.

    Try this:
    Code:
    Private Sub CmdBtnClrSelection_Click()
        Dim Ctl As Control
    
        '    MsgBox Screen.PreviousControl.Name
        Set Ctl = Screen.PreviousControl   '<<-- get the previous control
    
        If Ctl.ControlType = acTextBox Then   '<<-- ensure that the control is a text box
            Ctl = vbNullString  '<<--set the control value to a NULL string. "VALUE" is the default property, so you don't need to specify it.
        End If
    
        'clean up
        Set Ctl = Nothing
    
    End Sub

  3. #3
    KPAW is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2011
    Posts
    7
    Thanks very much for the assistance. The code worked fine.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help....

    Ready to mark this solved?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Better be careful with .Value property of an unbound control.
    create double click event like
    Private Sub Text0_DblClick(Cancel As Integer)
    MsgBox Me.Text0.Value
    End Sub
    enter a character (e.g. 9) and double click. Change to 8 and double click. Message should still say 9.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trying to clear a text box in a form
    By mcomp72 in forum Programming
    Replies: 6
    Last Post: 11-12-2017, 12:52 PM
  2. Save some text fields and clear others
    By whisp0214 in forum Forms
    Replies: 9
    Last Post: 10-25-2017, 03:48 PM
  3. Replies: 21
    Last Post: 01-21-2014, 05:04 PM
  4. Clear text box on form load
    By RoyLittle0 in forum Access
    Replies: 3
    Last Post: 03-05-2013, 05:40 AM
  5. Clear a form text box when user changes records
    By Artist.Anon in forum Forms
    Replies: 4
    Last Post: 08-19-2012, 07:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums