Enforcing Validity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Enforcing Validity

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-24 : 17:09:51

I have a VB routine that checks for the validity of the entered Admit_Dt.

Private Sub Admit_Dt_AfterUpdate()
If Not IsNull(Me.Admit_Dt.Value) Then
Me.Admit_Dt = fnDtValidx(Me.Admit_Dt.Value)
Else
Exit Sub
End If
End Sub

Unfortunately, even though my validation function will handle an erroneous entry:

Public Function fnDtValidx(vDt As Variant) As Variant
Dim vA As Variant
Dim vB As Variant
Dim vC As Variant
Dim vR As Integer

vA = Left(vDt, 4)
vB = Mid(vDt, 5, 2)
vC = Right(vDt, 2)
vR = 0
'Debug.Print vA & vB & vC

If CInt(vA) > 2002 And CInt(vA) < 2120 _
And CInt(vB) >= 1 And CInt(vB) <= 12 _
And CInt(vC) >= 1 And CInt(vC) <= 31 Then
Select Case CInt(vB)
Case 9, 4, 6, 11 And CInt(vC) > 30
MsgBox "Only 30 days in this month. Not a valid date using YYYYMMDD ", vbExclamation, MonthName(CInt(vB)) & " " & vC & ", " & vA
Exit Function
Case 2 And CInt(vC) > 29
vR = CInt(vA) Mod 4
If vR = 0 Then
MsgBox "Only 29 days in this month. Not a valid date using YYYYMMDD ", vbExclamation, MonthName(CInt(vB)) & " " & vC & ", " & vA
Exit Function
Else
MsgBox "Only 28 days in this month. Not a valid date, using YYYYMMDD ", vbExclamation, MonthName(CInt(vB)) & " " & vC & ", " & vA
Exit Function
End If
End Select
fnDtValid = vDt
Else
vR = InStr("01 02 03 04 05 06 07 08 09 10 11 12", vB)
If vR = 0 Then
MsgBox "Not a valid month in date using YYYYMMDD", vbExclamation
Else
MsgBox "Not a valid date using YYYYMMDD", vbExclamation, MonthName(CInt(vB)) & " " & vC & ", " & vA
End If
Exit Function
End If

End Function

Once it's done it goes to the next box instead of staying until this is resolved. Is there a loop I can write to keep the cursor there until Admit_Dt is a valid entry?

Thanks for any help you can offer.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-24 : 18:07:49

You have to call your validation function in the BeforeUpdate event, and if it's invalid call DoCmd.CancelEvent().

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-25 : 01:52:54

Isn't this the same as this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124423

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-28 : 13:15:35

No. Not exactly.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-28 : 17:46:57

quote:
Originally posted by robvolk

You have to call your validation function in the BeforeUpdate event, and if it's invalid call DoCmd.CancelEvent().

I did this in my BeforeUpdate

Private Sub Process_Dt_BeforeUpdate(cancel As Integer)
If IsNull(Me.Process_Dt.Value) Then
cancel = True
Me.Process_Dt.SetFocus
End If
End Sub

And this in my AfterUpdate

Private Sub Process_Dt_AfterUpdate()
If Not IsNull(Me.Process_Dt.Value) Then
Me.Process_Dt = fnDtValidx(Me.Process_Dt.Value)
Else
Me.Process_Dt.SetFocus
DoCmd.CancelEvent
Exit Sub
End If
End Sub

And neither keeps the cursor in the box until a valid date as described by my previously presented function.
Is there a way to FORCE the user to ENTER a CORRECT DATE before being allowed to proceed to the next box in the form?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 19:02:32

Did you try DoCmd.CancelEvent in the BeforeUpdate event (like I mentioned earlier)? I tried it on a test form and it worked fine for me.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-05-04 : 12:39:38

Thanks, Rob!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources