TextBox Data Validation

  • Thread starter Thread starter Rohit Thomas
  • Start date Start date
R

Rohit Thomas

Hello,

I have a textbox in a form that's used to get a filename.
This filename has a specific naming convention. I would
like to setup data validation so that when a user inputs
the filename, it would only accept the data if it passes
the validation. The naming conventions is as follows:
DMMDDYY.CEX, D is constant, MM is month, DD is day, YY is
year, and CEX is the file extension.Is it possible to do
this?

Thanks in advance,
Rohit
 
Yes, you can use the textbox's BeforeUpdate event to run code that will do
this validation. Something like this on that event:

Private Sub TextBoxName_BeforeUpdate(Cancel As Integer)
Dim strDate As String
If Right(Me.TextBoxName.Value, InStrRev(Me.TextBoxName.Value, _
".") - 1) <> "CEX" Then
MsgBox "You have entered the wrong file extension."
Cancel = True
ElseIf IsNumeric(Left(Me.TextBoxName.Value, 1)) = False Then
MsgBox "You have not entered a constant as the first character."
Cancel = True
ElseIf Val(Mid(Me.TextBoxName.Value, 2, 2)) < 1 Or _
Val(Mid(Me.TextBoxName.Value, 2, 2)) > 12 Then
MsgBox "You have not entered a valid month number."
Cancel = True
Else
strDate = Mid(Me.TextBoxName.Value, 2, 6)
If Not DateSerial(CInt(Right(strDate, 2)), CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date() - 180 And Date() Then
MsgBox "You have entered a date that is more than six months ago
or is later than today."
Cancel = True
End If
End If
End Sub


You can add / use other criteria as you desire.
 
Ken,

I am studying up on VBA and would like to use the code you
provided. My lack of knowledge is preventing me from
fixing a problem with the last Else and Then statement in
the code. Can you explain the functions in the last
statement and take a look at the code to identify the
problem.

Thanks,
Rohit
 
Sorry, but can you point specifically to the line(s) that are of question?
And what problem are you seeing -- is there a specific error message on a
particular line of code? Let me know and I'll be happy to provide more info!
 
I copied the code you provided to access and the last Else
statement remained in red...I'm assuming that means that's
something is incorrect. As you can see I'm new to this and
still learning and lack the know how to fix it.
 
I'm guessing that you've inadvertently included a "line break" that the
newsreader inserted into the code that I posted.

Copy and paste the following into the module and see if this is better
(note: the _ character [when preceded by a space] is used to allow a code
line to continue onto the next line, as if it were all written on one line):

Private Sub TextBoxName_BeforeUpdate(Cancel As Integer)
Dim strDate As String
If Right(Me.TextBoxName.Value, _
InStrRev(Me.TextBoxName.Value, _
".") - 1) <> "CEX" Then
MsgBox "You have entered the wrong file extension."
Cancel = True
ElseIf IsNumeric(Left(Me.TextBoxName.Value, 1)) = _
False Then
MsgBox _
"You have not entered a constant as the first character."
Cancel = True
ElseIf Val(Mid(Me.TextBoxName.Value, 2, 2)) < 1 Or _
Val(Mid(Me.TextBoxName.Value, 2, 2)) > 12 Then
MsgBox "You have not entered a valid month number."
Cancel = True
Else
strDate = Mid(Me.TextBoxName.Value, 2, 6)
If Not DateSerial(CInt(Right(strDate, 2)), _
CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date() - 180 _
And Date() Then
MsgBox _
"You have entered a date that is more" & _
" than six months ago or is later than today."
Cancel = True
End If
End If
End Sub
 
Ken,

I copied the code as is to my project and got the same
result. The code below was in red after pasting it to the
module.

If Not DateSerial(CInt(Right(strDate, 2)), _
CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date() - 180 _
And Date() Then
-----Original Message-----
I'm guessing that you've inadvertently included a "line break" that the
newsreader inserted into the code that I posted.

Copy and paste the following into the module and see if this is better
(note: the _ character [when preceded by a space] is used to allow a code
line to continue onto the next line, as if it were all written on one line):

Private Sub TextBoxName_BeforeUpdate(Cancel As Integer)
Dim strDate As String
If Right(Me.TextBoxName.Value, _
InStrRev(Me.TextBoxName.Value, _
".") - 1) <> "CEX" Then
MsgBox "You have entered the wrong file extension."
Cancel = True
ElseIf IsNumeric(Left(Me.TextBoxName.Value, 1)) = _
False Then
MsgBox _
"You have not entered a constant as the first character."
Cancel = True
ElseIf Val(Mid(Me.TextBoxName.Value, 2, 2)) < 1 Or _
Val(Mid(Me.TextBoxName.Value, 2, 2)) > 12 Then
MsgBox "You have not entered a valid month number."
Cancel = True
Else
strDate = Mid(Me.TextBoxName.Value, 2, 6)
If Not DateSerial(CInt(Right(strDate, 2)), _
CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date() - 180 _
And Date() Then
MsgBox _
"You have entered a date that is more" & _
" than six months ago or is later than today."
Cancel = True
End If
End If
End Sub

--
Ken Snell
<MS ACCESS MVP>

I copied the code you provided to access and the last Else
statement remained in red...I'm assuming that means that's
something is incorrect. As you can see I'm new to this and
still learning and lack the know how to fix it.

that
are of question? to
provide more info! code
you statement
in Or
_ day,
YY
Click to expand...


.
Click to expand...
 
Ah, that's different from what I'd understood...I was understanding that the
"Else" line was in red.

This is my error. I overlooked that the Between...And syntax doesn't work in
If..Then statement.

Try this line of code in place of the one that's in red:

If DateDiff("d",DateSerial(CInt(Right(strDate, 2)), _
CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) , Date()) > 180 Then

--
Ken Snell
<MS ACCESS MVP>

Rohit Thomas said:
Ken,

I copied the code as is to my project and got the same
result. The code below was in red after pasting it to the
module.

If Not DateSerial(CInt(Right(strDate, 2)), _
CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date() - 180 _
And Date() Then
-----Original Message-----
I'm guessing that you've inadvertently included a "line break" that the
newsreader inserted into the code that I posted.

Copy and paste the following into the module and see if this is better
(note: the _ character [when preceded by a space] is used to allow a code
line to continue onto the next line, as if it were all written on one line):

Private Sub TextBoxName_BeforeUpdate(Cancel As Integer)
Dim strDate As String
If Right(Me.TextBoxName.Value, _
InStrRev(Me.TextBoxName.Value, _
".") - 1) <> "CEX" Then
MsgBox "You have entered the wrong file extension."
Cancel = True
ElseIf IsNumeric(Left(Me.TextBoxName.Value, 1)) = _
False Then
MsgBox _
"You have not entered a constant as the first character."
Cancel = True
ElseIf Val(Mid(Me.TextBoxName.Value, 2, 2)) < 1 Or _
Val(Mid(Me.TextBoxName.Value, 2, 2)) > 12 Then
MsgBox "You have not entered a valid month number."
Cancel = True
Else
strDate = Mid(Me.TextBoxName.Value, 2, 6)
If Not DateSerial(CInt(Right(strDate, 2)), _
CInt(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date() - 180 _
And Date() Then
MsgBox _
"You have entered a date that is more" & _
" than six months ago or is later than today."
Cancel = True
End If
End If
End Sub

--
Ken Snell
<MS ACCESS MVP>

I copied the code you provided to access and the last Else
statement remained in red...I'm assuming that means that's
something is incorrect. As you can see I'm new to this and
still learning and lack the know how to fix it.

Else
strDate = Mid(Me.TextBoxName.Value, 2, 6)
If Not DateSerial(CInt(Right(strDate, 2)), CInt
(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date () -
180 And Date() Then

-----Original Message-----
Sorry, but can you point specifically to the line(s) that
are of question?
And what problem are you seeing -- is there a specific
error message on a
particular line of code? Let me know and I'll be happy to
provide more info!

--
Ken Snell
<MS ACCESS MVP>

"Rohit Thomas" <[email protected]>
wrote in message
Ken,

I am studying up on VBA and would like to use the code
you
provided. My lack of knowledge is preventing me from
fixing a problem with the last Else and Then statement
in
the code. Can you explain the functions in the last
statement and take a look at the code to identify the
problem.

Thanks,
Rohit

-----Original Message-----
Yes, you can use the textbox's BeforeUpdate event to
run
code that will do
this validation. Something like this on that event:

Private Sub TextBoxName_BeforeUpdate(Cancel As Integer)
Dim strDate As String
If Right(Me.TextBoxName.Value, InStrRev
(Me.TextBoxName.Value, _
".") - 1) <> "CEX" Then
MsgBox "You have entered the wrong file
extension."
Cancel = True
ElseIf IsNumeric(Left(Me.TextBoxName.Value, 1)) =
False Then
MsgBox "You have not entered a constant as the
first character."
Cancel = True
ElseIf Val(Mid(Me.TextBoxName.Value, 2, 2)) < 1 Or
_
Val(Mid(Me.TextBoxName.Value, 2, 2)) > 12 Then
MsgBox "You have not entered a valid month
number."
Cancel = True
Else
strDate = Mid(Me.TextBoxName.Value, 2, 6)
If Not DateSerial(CInt(Right(strDate, 2)), CInt
(Left(strDate, 2)), _
CInt(Mid(strDate, 3, 2))) Between Date () -
180 And Date() Then
MsgBox "You have entered a date that is
more
than six months ago
or is later than today."
Cancel = True
End If
End If
End Sub


You can add / use other criteria as you desire.
--
Ken Snell
<MS ACCESS MVP>



"Rohit Thomas" <[email protected]>
wrote in message
Hello,

I have a textbox in a form that's used to get a
filename.
This filename has a specific naming convention. I
would
like to setup data validation so that when a user
inputs
the filename, it would only accept the data if it
passes
the validation. The naming conventions is as follows:
DMMDDYY.CEX, D is constant, MM is month, DD is day,
YY
is
year, and CEX is the file extension.Is it possible
to do
this?

Thanks in advance,
Rohit



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top