locked
Clear date TextBox control RRS feed

  • Question

  • I have a Subform (with a Date field), a TextBox (bound to DatePicker) and a Button(Clear). I use DatePicker to select a date and Subform is filtered accordingly. By using Button or keyboard backspace TextBox is cleared.  When I enter a number (2 for example) again Subform only displays dates that have (2) in Day, Month or Year. If I use keyboard backspace  to clear TextBox, Subform refreshes and displays all the records. When I select another date using DatePicker then number (2) is replaced whith the selected date and records are filtered.

    Problem arises when the TextBox has partial value (not complete date) and I click (Clear) Button to Clear TextBox.

    I tried VBA: TextBox = "" , TextBox = NULL, Me!TextBox ="" ... I get "The value you entered isn't valid for this field" error.

    Any help is appreciated.

    Wednesday, June 6, 2018 7:51 PM

Answers

  • theDBguy thanks for quick response. I tried your code with no luck. Could it be done using a macro (to clear the text box)?

    Hi,

    I was afraid the internal validation would fire before the update event. In this case, you will probably have to use the Form's Error event. For example, try the following:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 Then
        Response = acDataErrContinue
        Me.txtDate.Undo
        DoCmd.Requery "Query1 subform"
    End If
    
    End Sub

    Hope it helps...

    • Marked as answer by Savalan Thursday, June 7, 2018 11:41 PM
    Thursday, June 7, 2018 2:54 PM

All replies

  • Hi,

    The problem, I think, is since you specified the textbox to be a date value, Access validates the entry to make sure it is a valid date. Now, I am not sure if you can trap this error in the BeforeUpdate event of the textbox, but I think it's worth a try. For example:

    Private Sub TextboxName_BeforeUpdate(Cancel As Integer)
         If Not IsDate(Me.TextboxName) Then
              Cancel = True
              Me.TextboxName.Undo
         End If
    End Sub

    Hope it helps...

    Wednesday, June 6, 2018 8:06 PM
  • theDBguy thanks for quick response. I tried your code with no luck. Could it be done using a macro (to clear the text box)?

    Thursday, June 7, 2018 1:06 AM
  • Hi Savalan,

    Could you share us a simple database which could reproduce your issue?

    I made a test with code below, but I fail to reproduce your issue.

    Private Sub Command17_Click()
    'BirthDay.SetFocus
    'MsgBox BirthDay.Text
    BirthDay.SetFocus
    Me.BirthDay.Text = ""
    End Sub
    

    I assume your issue is related with filterring the records with the value from TextBox datepicker. How did you filter the records, I think you need to check the TextBox value before using the value filterring the records.

    While the TextBox is empty, you need to return all records intead of passing the filter condition.

    Best Regards,

    Tao Zhou



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 7, 2018 6:42 AM
  • Hi Tao,

    Steps:

    Create tbl_Members:
        ID = autonumber      'PK
        n_name = short text
        DOB = Date/Time

    '==============================
    Create Form1:
        1 Unbound TextBox 'Properties:: Format [Long Date] -- Show Date Picker [For Dates] -- Name [txtDate]
        1 Button       'Properties:: Name [cmdClear]

    '==============================
    Create Query1:
        DOB > Criteria:

    Like "*" & [Forms]![Form1]![txtDate].[Text] & "*"



    '===============================
    Drag Query1 onto Form1 to create "Query1 subform"

    '===============================

    VBA:

    Option Compare Database
    Option Explicit
    
    Private Sub Form_Load()
        txtDate = ""
        txtDate.SetFocus
    End Sub
    
    Private Sub txtDate_Change()
        DoCmd.Requery "Query1 subform"
    End Sub
    
    Private Sub cmdClear_Click()
        txtDate = ""
        txtDate.SetFocus
        DoCmd.Requery "Query1 subform"
    End Sub

    Whether I select the date from Date Picker or type in, filtering works fine and Button clears the TextBox.

    Also, as I enter numbers, the filter narrows down the search accordingly. but when I attempt to clear the partially entered number the I get the error message. Following image shows the error.

    Error Message

    Thank you.

    Thursday, June 7, 2018 12:13 PM
  • theDBguy thanks for quick response. I tried your code with no luck. Could it be done using a macro (to clear the text box)?

    Hi,

    I was afraid the internal validation would fire before the update event. In this case, you will probably have to use the Form's Error event. For example, try the following:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 Then
        Response = acDataErrContinue
        Me.txtDate.Undo
        DoCmd.Requery "Query1 subform"
    End If
    
    End Sub

    Hope it helps...

    • Marked as answer by Savalan Thursday, June 7, 2018 11:41 PM
    Thursday, June 7, 2018 2:54 PM
  • Thank you very much theDBguy. It works as expected.

    Regards,

    Savalan

    Thursday, June 7, 2018 11:43 PM
  • Hi Savalan,

    You're welcome. Glad we could assist. Good luck with your project.

    Friday, June 8, 2018 3:34 AM