Ms-Access中VBA文本框日期输入验证异常问题

Let’s break down why your current code shows the error message every time, and how to fix it properly:

The Problems with Your Original Code #

• You don’t exit the sub after successful execution, so the code always hits the Baddates label even when no error occurs.
• FormatDateTime doesn’t accept a string format like "yyyy/mm/dd" as its second argument—you need to use the Format function instead for custom date formatting.
• There’s no logic to keep focus on the DateCutting textbox when invalid input is entered, so it still jumps to the next control.

Corrected VBA Code #

Here’s the revised LostFocus event code that fixes all these issues:

Private Sub DateCutting_LostFocus()
    Dim inputDate As String
    Dim validatedDate As Date
    
    inputDate = Me.DateCutting.Text
    
    ' First, check if the input is a valid date
    If IsDate(inputDate) Then
        ' Convert to date and format it as yyyy/mm/dd
        validatedDate = CDate(inputDate)
        Me.DateCutting.Text = Format(validatedDate, "yyyy/mm/dd")
        ' Update TextBox1 with the formatted date
        Me.TextBox1.Text = Me.DateCutting.Text
    Else
        ' Show error message with clear guidance
        MsgBox "Please Insert Correct Date! (Use format: yyyy/mm/dd)", vbExclamation, "Invalid Date"
        ' Force focus back to the textbox to prevent moving to next control
        Me.DateCutting.SetFocus
        ' Select the invalid text so user can easily overwrite it
        Me.DateCutting.SelStart = 0
        Me.DateCutting.SelLength = Len(inputDate)
    End If
End Sub

Key Improvements Explained #

• IsDate Validation: This built-in function checks if the input can be converted to a valid date, catching invalid values like month 15 or day 31 in February automatically.
• Focus Control: When invalid input is detected, SetFocus locks the cursor on DateCutting so the user can’t proceed until they fix the date.
• Proper Formatting: Using Format() instead of FormatDateTime lets you specify your exact desired "yyyy/mm/dd" format.
• User-Friendly Selection: Highlighting the entire invalid text makes it easier for the user to correct their input without manual deletion.

Alternative: Using Error Handling (If You Prefer) #

If you want to stick with error handling instead of IsDate, adjust your original code to only trigger the message when an actual error occurs:

Private Sub DateCutting_LostFocus()
    On Error GoTo Baddates
    
    Dim myDate As Date
    myDate = CDate(Me.DateCutting.Text)
    Me.DateCutting.Text = Format(myDate, "yyyy/mm/dd")
    Me.TextBox1.Text = Me.DateCutting.Text
    
    ' Exit sub before hitting error handler if no issues
    Exit Sub
    
Baddates:
    MsgBox "Please Insert Correct Date! (Use format: yyyy/mm/dd)", vbExclamation, "Invalid Date"
    Me.DateCutting.SetFocus
    Me.DateCutting.SelStart = 0
    Me.DateCutting.SelLength = Len(Me.DateCutting.Text)
End Sub

The critical addition here is the Exit Sub before the Baddates label—this ensures the error message only pops up when an actual date conversion failure happens.

内容的提问来源于stack exchange，提问作者Hadi