Validations

[Tom Wickerath]

Hi Dinger188,

Try the following variation of your code:

'***************BEGIN CODE*********************

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Const conBtns As Integer = vbOKOnly + vbCritical + _
vbDefaultButton2 + vbApplicationModal
Dim intUserResponse As Integer

If IsNull(Me.Date) Then
Cancel = True
intUserResponse = MsgBox("Complete the INCIDENT DATE Field!", _
conBtns, "STOP! Incomplete
Field")
Me.Date.SetFocus
ElseIf IsNull(Me.Nature_of_call) Then
Cancel = True
intUserResponse = MsgBox("Complete the NATURE OF CALL Field!", _
conBtns, "STOP! Incomplete
Field")
Me.Nature_of_call.SetFocus
ElseIf IsNull(Me.Called_By) Then
Cancel = True
intUserResponse = MsgBox("Complete the CALLED BY Field!", _
conBtns, "STOP! Incomplete
Field")
Me.Called_By.SetFocus
ElseIf (Me.Nature_of_call) = "Fire" Then
If IsNull(Me.Type_of_Fire) Then
Cancel = True
intUserResponse = MsgBox("Complete the TYPE OF FIRE Field!", _
conBtns, "STOP! Incomplete
Field")
Me.Type_of_Fire.SetFocus
ElseIf IsNull(Me.Cause_of_Fire) Then
Cancel = True
intUserResponse = MsgBox("Complete the CAUSE OF FIRE Field!", _
conBtns, "STOP! Incomplete
Field")
Me.Cause_of_Fire.SetFocus
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_BeforeUpdate..."
Resume ExitProc
End Sub

'***************END CODE**********************

A couple of suggestions:

1.) Date is a reserved word. You should avoid using any reserved words, or
special characters (including spaces) in anything that you assign a name to
in Access. Here are some reference Knowledge Base articles:

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

Here are two other articles on the topic of naming conventions:
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

2.) You might want to consider using periods, instead of exclaimation marks,
in the text of your message boxes. Your current feedback "shouts" at your
users.

By the way, my step-dad is a retired Captian of the Ellensburg Fire
Department, located in Ellensburg, WA. (USA).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Dinger188]

On a form, I have a combo box with a list of "Nature of Calls" and 2
additional combo boxes labeled Type of Fire and Cause of fire. If "Fire" is
picked from the nature of call I would like to validate that the Type of Fire
and Cause of Fire was completed. What I came up with works, but only when
"Fire" is not picked. How do I do this correctly??

Here is what I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Const conBtns As Integer = vbOKOnly + vbCritical + vbDefaultButton2 +
vbApplicationModal
Dim intUserResponse As Integer

If IsNull(Me.Date) Then
Cancel = True
intUserResponse = MsgBox("Complete the INCIDENT DATE Field!", conBtns,
"STOP! Incomplete Field")
Me.Date.SetFocus

ElseIf IsNull(Me.Nature_of_Call) Then
Cancel = True
intUserResponse = MsgBox("Complete the NATURE OF CALL Field!", conBtns,
"STOP! Incomplete Field")
Me.Nature_of_Call.SetFocus

ElseIf IsNull(Me.Called_By) Then
Cancel = True
intUserResponse = MsgBox("Complete the CALLED BY Field!", conBtns, "STOP!
Incomplete Field")
Me.Called_By.SetFocus

ElseIf (Me.Nature_of_Call) = "Fire" Then

ElseIf IsNull(Me.Type_of_Fire) Then
Cancel = True
intUserResponse = MsgBox("Complete the TYPE OF FIRE Field!", conBtns, "STOP!
Incomplete Field")
Me.Type_of_Fire.SetFocus

ElseIf IsNull(Me.Cause_of_Fire) Then
Cancel = True
intUserResponse = MsgBox("Complete the CAUSE OF FIRE Field!", conBtns,
"STOP! Incomplete Field")
Me.Cause_of_Fire.SetFocus

End If

End Sub

 

[Tom Wickerath]

Hi Dan,

You can use:
If Me.IncidentDate >= #4/1/2006# And Me.IncidentDate < #4/1/2007# Then

but, I'm not sure that you really want to hard-code any dates into your VBA
code, or why you would want to exclude dates in this range. Presumably, the
IncidentDate for a fire cannot occur in the future, unless it is a planned
event. So, I would think that you would want to limit IncidentDates to be no
greater than the current date. You might also want to allow IncidentDate
values that are some range prior to today's date, such as 30 days prior. Are
you sure you don't want something like this instead?

Remove the line of code that reads:
Me.IncidentDate.SetFocus

It is not needed, and actually causes an error, since you are cancelling the
before_update of the control. Also, you can substitute:
vbCrLf for Chr(13) & Chr(10).

Here is a suggested change, assuming you want to allow a date that is within
the past 30 days:

'***************Begin Code**********

Private Sub IncidentDate_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Const conBtns As Integer = vbOKOnly + vbCritical + vbApplicationModal
Dim intUserResponse As Integer


If Me.IncidentDate >= Date + 1 Or Me.IncidentDate < Date - 30 Then
Cancel = True
intUserResponse = MsgBox("CORRECT THE INCIDENT DATE!" _
& vbCrLf & vbCrLf & _
Me.IncidentDate & " is not within the last
30 days.", _
conBtns, "STOP! Date Not in Range")
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure IncidentDate_BeforeUpdate..."
Resume ExitProc
End Sub

'***************End Code************


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Tom, Thanks for your response, it worked wonderfully... I was wondering if
you or anyone could help me out with my next question. I'd like to validate
the date range in the date (now incident date) field. I can get it to work
with a validation rule but I'd like to use an event procedure. Here is what I
came up with:

***Start***

Private Sub IncidentDate_BeforeUpdate(Cancel As Integer)

Const conBtns As Integer = vbOKOnly + vbCritical + vbApplicationModal
Dim intUserResponse As Integer

If (Me.IncidentDate) >= #4/1/2006# And <#4/1/2007# Then
Cancel = True
intUserResponse = MsgBox("CORRECT THE INCIDENT DATE!" & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & "Between 4/1/2006 and 3/31/2007", conBtns,
"STOP! Date Not in Range")
Me.IncidentDate.SetFocus

End If

End Sub


***End***

I get a error, "Complie error: Expected: expression" and it highligts "<"

What did I do wrong??


Good catch on this being for the FD. I belong to a career dept in Lyons, NY
(Upstate, NY)

Thanks,

Dan

 

[Dinger188]

Tom, Thanks for your response, it worked wonderfully... I was wondering if
you or anyone could help me out with my next question. I'd like to validate
the date range in the date (now incident date) field. I can get it to work
with a validation rule but I'd like to use an event procedure. Here is what I
came up with:

***Start***

Private Sub IncidentDate_BeforeUpdate(Cancel As Integer)

Const conBtns As Integer = vbOKOnly + vbCritical + vbApplicationModal
Dim intUserResponse As Integer

If (Me.IncidentDate) >= #4/1/2006# And <#4/1/2007# Then
Cancel = True
intUserResponse = MsgBox("CORRECT THE INCIDENT DATE!" & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & "Between 4/1/2006 and 3/31/2007", conBtns,
"STOP! Date Not in Range")
Me.IncidentDate.SetFocus

End If

End Sub


***End***

I get a error, "Complie error: Expected: expression" and it highligts "<"

What did I do wrong??


Good catch on this being for the FD. I belong to a career dept in Lyons, NY
(Upstate, NY)

Thanks,

Dan