Date Validation function

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Hello, I have a form with two text boxes on it "txtStartDate" and
"txtEndDate" and I want to use a Private Function ValidDates() As Boolean for
my validation. What am I doing wrong? Its using the (MsgOK) to bring up the
office assistant which works. the thing is I cant get the form to open if I
have valid dates in the txt boxes ect. Thank! Below is what I want and below
that is what I have working but as I said I want to use the (MsgOK) to bring
up the office assistant. Please help... Thanks!

'*********************************************
'* Function to validate a Start and End date *
'*********************************************
Private Function ValidDates() As Boolean


If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) = False Then
MsgOK "Both Start and End Dates are Required for the Reports.",
"Required"
ValidDates = False
Exit Function
End If

If Me.txtStartDate > Me.txtEndDate Then
MsgOK "Start Date must be EQUAL TO or LESS THAN End Date."
ValidDates = False
Exit Function
End If

End Function


'*********************************************
'* Function to validate a Start and End date *
'*********************************************
Private Function ValidDates() As Boolean

Dim strMsg As String

ValidDates = True

If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
If Me.txtStartDate > Me.txtEndDate Then
strMsg = "Start Date must be EQUAL TO or LESS THAN End Date."
End If
Else
strMsg = "Both Start and End Dates are Required for the Reports."
End If

If Len(strMsg) Then
MsgBox strMsg, vbOKOnly, "Date Entry Error"
ValidDates = False
End If

End Function
 
Hello, I have a form with two text boxes on it "txtStartDate" and
"txtEndDate" and I want to use a Private Function ValidDates() As Boolean for
my validation. What am I doing wrong? Its using the (MsgOK) to bring up the
office assistant which works. the thing is I cant get the form to open if I
have valid dates in the txt boxes ect. Thank! Below is what I want and below
that is what I have working but as I said I want to use the (MsgOK) to bring
up the office assistant. Please help... Thanks!
Click to expand...

Well, you have the function defined TWICE - same name, slightly different
code; but noplace do you describe where and how you are calling the function.
Context?
 
John im calling from buttons on my form. Here for example is one button that
is using the function "If ValidDates()"

Private Sub cmdOption6_Click()
On Error GoTo Err_cmdOption6_Click

Dim stDocName As String
Dim stLinkCriteria As String

If ValidDates() Then
If Me.cboProduct & "" <> "" Then
stDocName = "rptRollsetFootage"
DoCmd.OpenReport stDocName, acPreview
Else
msgbox "Must Select Product Name", vbOKOnly, "Must Select
Product Name"
End If
End If

Exit_cmdOption6_Click:
Exit Sub

Err_cmdOption6_Click:
msgbox Err.Description
Resume Exit_cmdOption6_Click
End Sub
 
John im calling from buttons on my form. Here for example is one button that
is using the function "If ValidDates()"

Private Sub cmdOption6_Click()
On Error GoTo Err_cmdOption6_Click

Dim stDocName As String
Dim stLinkCriteria As String

If ValidDates() Then
If Me.cboProduct & "" <> "" Then
stDocName = "rptRollsetFootage"
DoCmd.OpenReport stDocName, acPreview
Else
msgbox "Must Select Product Name", vbOKOnly, "Must Select
Product Name"
End If
End If

Exit_cmdOption6_Click:
Exit Sub

Err_cmdOption6_Click:
msgbox Err.Description
Resume Exit_cmdOption6_Click
End Sub
Click to expand...

Ok... if ValidDates() returns False, this sub simply exits without doing
anything. I think it's just doing exactly what you ask it to (which is not
necessarily what you want).

I would be strongly inclined to do field validation in the Form's BeforeUpdate
event, and set its Cancel argument to True after prompting to fix the
problems. As it is your ValidDates() routine prompts the user but the code
just keeps running and doesn't check to see if they've fixed the problem.
 
John, I tried the vba bellow and it givs all the correct messages but still
opens the form after I close the message box? Any ideas...

Thanks!

Private Function ValidDates() As Boolean

Dim strMsg As String

ValidDates = True

If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
If Me.txtStartDate > Me.txtEndDate Then
'strMsg = "Start Date must be EQUAL TO or LESS THAN End Date."
MsgOK "Start Date must be EQUAL TO or LESS THAN End Date."
End If
Else
'strMsg = "Both Start and End Dates are Required for the Reports."
MsgOK "Both Start and End Dates are Required for the Reports.",
"Required"
End If

If Len(strMsg) Then
'msgbox strMsg, vbOKOnly, "Date Entry Error"
MsgOk strMsg, "Date Entry Error"
ValidDates = False
End If

End Function
 
John, I tried the vba bellow and it givs all the correct messages but still
opens the form after I close the message box? Any ideas...
Click to expand...

Well, there is ABSOLUTELY NOTHING in your code about opening a form.

Your function returns False. That's all that happens. The routine calling
ValidDates() needs to take some action based on whether the value returned by
ValidDates is True or False. The code you posted earlier simply exits without
doing anything if it's False.
 
John, I tried the vba bellow and it givs all the correct messages but still
opens the form after I close the message box? Any ideas...
Click to expand...

My apologies, Chad. I reread the calling routine and it looks like it SHOULD
work. I don't see why it should be necessary to do so, but try comparing the
result of ValidDates() to True:

Private Sub cmdOption6_Click()
On Error GoTo Err_cmdOption6_Click

Dim stDocName As String
Dim stLinkCriteria As String

If ValidDates()=True Then
If Me.cboProduct & "" <> "" Then
stDocName = "rptRollsetFootage"
DoCmd.OpenReport stDocName, acPreview
Else
msgbox "Must Select Product Name", vbOKOnly, "Must Select
Product Name"
End If
End If

Exit_cmdOption6_Click:
Exit Sub

Err_cmdOption6_Click:
msgbox Err.Description
Resume Exit_cmdOption6_Click
End Sub
 
John, I have tried the "If ValidDates()=True Then" and it still opens the
form. I got an answer to the post where there is a sample I talked about in
an earler post here.
http://www.access-programmers.co.uk/forums/showthread.php?t=144784

the guy helping wrote and said I should be using:

'*********************************************
'* Function to validate a Start and End date *
'*********************************************
Private Function ValidDates() As Boolean
Dim strMsg As String

If IsDate(Me.txtStartDate) = True And IsDate(Me.txtEndDate) = True Then
If Me.txtStartDate > Me.txtEndDate Then
strMsg = "Start Date must be EQUAL TO or LESS THAN End Date."
End If
Else
strMsg = "Both the Start Date and End Date are required to display a
Reports."
End If

If Len(strMsg) = 0 Then
Validates = True
Else
MsgOK strMsg, "Date Entry Error"
ValidDates = False
End If
End Function


Which works as in it gives me all the correct msg boxes BUT, when I meet all
the criteria and click the button to open the form it does nothing? I even
have the "If ValidDates()=True Then". Take a look at the sample I posted so
see for your self.

Thanks,
Chad
 
If that's an actual copy-and-paste of your code, you've got a typo in it.

When the message length is 0, you're setting the wrong variable: Validates
instead of ValidDates.

If Len(strMsg) = 0 Then
Validates = True
Else
MsgOK strMsg, "Date Entry Error"
ValidDates = False
End If

The fact that your code works at all indicates to me that you haven't told
Access to require definition of all variables. (You can tell by the presence
of the line Option Explicit at the top of each module). While it can be a
pain correcting all your oversights when you first add that line, it's
definitely worth it in the end: you would have caught that typo immediately.

To have Access add that line by default, go into the VB Editor and select
Tools | Options from the menu bar. Look on the Editor tab: the checkbox
"Require Variable Declaration" should be checked. (You'll have to manually
add the line to each existing module)
 
Great it works! I have one more msg box and its a Yes No box. Instead of
using the
MsgOK like before to bring up a Office assistant OK box I need to use a
MsgYN to bring up an office assistant Yes No box. What would I have to change
to the code I have to be able to implement this? Thanks!

Private Sub txtEmployeeTime_AfterUpdate()
Dim strMsg As String

strMsg = "Was overtime worked? If so, " & _
"OT Status box will be checked"

If Me.txtEmployeeTime <> 8 Then
If msgbox(strMsg, vbYesNo) = vbYes Then
Me.chkStatus = True
Me.txtDTRegular = Round((txtAccTimeWorked) - (txtMinutes / 60), 2)
Else

End If
End If

End Sub
 
Back
Top