Multi Field Validation

  • Thread starter Thread starter AJ Raiber
  • Start date Start date
A

AJ Raiber

I need to see if it is possible in Access 2000 to create a
validation rule to prevent duplicate input of timeframes
based on name and the starting and end dates.

I have three fields affected by this, the SSN field which
autofills the name in a subform, the starting date and the
ending date. I need to prevent input of a group of dates
if they overlap with another group of dates for that SSN.

Is this possible and how can it be done if it is? Thank
you all!

AJ
 
Cancel the BeforeUpdate event procedure of the form if this record would be
a duplicate.

Two records overlap if both these are true:
- A starts before B ends, and
- B starts before A ends.
Use that combined with the SSN to DLookup() the table to see if there is a
clash.

The following aircode assumes that all 3 fields are required, and that you
have a primary key named "ID" which is used to ensure that changes to an
existing record are not reported as a clash with itself.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.SSN) OR IsNull(Me.StartDate) OR IsNull(Me.EndDate) Then
Cancel = True
MsgBox "You must supply SSN, Start Date, and End Date."
Else
strWhere = "([SSN] = """ & Me.SSN & """) AND (" _
& Format(Me.[StartDate], conJetDate) & _
" < [EndDate]) AND ([StartDate] < " & _
Format(Me.[EndDate], conJetDate & ")"
If Not Me.NewRecord Then
'Existing record does not clash with itself.
strWhere = strWhere & " AND ([ID] <> " & Me.ID & ")"
End If
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with ID " & varResult
End If
End If
End Sub
 
Allen,

When I input this code and after changing the
variables to match what I needed and adding one ), it
worked wonderfully the first time. However when I exited
the form and came back in, it didn't do a thing. The code
as adjusted is below. Can you see any reason it would
work on the first time but not thereafter?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#yyyy\/mm\/dd\#"

If IsNull(Me.SSN) Or IsNull(Me.[Start Date]) Or IsNull
(Me.[End Date]) Then
Cancel = True
MsgBox "You must supply SSN, Start Date, and End
Date."
Else
strWhere = "([SSN] = """ & Me.SSN & """) AND (" _
& Format(Me.[Start Date], conJetDate) & _
" < [End Date]) AND ([Start Date] < " & _
Format(Me.[End Date], conJetDate & ")")
If Not Me.NewRecord Then
'Existing record does not clash with itself.
strWhere = strWhere & " AND ([ctrl nbr] <> " &
Me.[ctrl nbr] & ")"
End If
varResult = DLookup("[ctrl nbr]", "[LEAVES]",
strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with CTRL NBR " & varResult
End If
End If
End Sub

___________________________________________________________

Thank you.

-----Original Message-----
Cancel the BeforeUpdate event procedure of the form if this record would be
a duplicate.

Two records overlap if both these are true:
- A starts before B ends, and
- B starts before A ends.
Use that combined with the SSN to DLookup() the table to see if there is a
clash.

The following aircode assumes that all 3 fields are required, and that you
have a primary key named "ID" which is used to ensure that changes to an
existing record are not reported as a clash with itself.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.SSN) OR IsNull(Me.StartDate) OR IsNull (Me.EndDate) Then
Cancel = True
MsgBox "You must supply SSN, Start Date, and End Date."
Else
strWhere = "([SSN] = """ & Me.SSN & """) AND (" _
& Format(Me.[StartDate], conJetDate) & _
" < [EndDate]) AND ([StartDate] < " & _
Format(Me.[EndDate], conJetDate & ")"
If Not Me.NewRecord Then
'Existing record does not clash with itself.
strWhere = strWhere & " AND ([ID] <> " & Me.ID & ")"
End If
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with ID " & varResult
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


AJ Raiber said:
I need to see if it is possible in Access 2000 to create a
validation rule to prevent duplicate input of timeframes
based on name and the starting and end dates.

I have three fields affected by this, the SSN field which
autofills the name in a subform, the starting date and the
ending date. I need to prevent input of a group of dates
if they overlap with another group of dates for that SSN.

Is this possible and how can it be done if it is? Thank
you all!

AJ
Click to expand...


.
Click to expand...
 
You could try:
Debug.Print strWhere

From there you could open the Immediate Window (Ctrl+G) and test the
DLookup() to see why it's not finding your results.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


AJ Raiber said:
Allen,

When I input this code and after changing the
variables to match what I needed and adding one ), it
worked wonderfully the first time. However when I exited
the form and came back in, it didn't do a thing. The code
as adjusted is below. Can you see any reason it would
work on the first time but not thereafter?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#yyyy\/mm\/dd\#"

If IsNull(Me.SSN) Or IsNull(Me.[Start Date]) Or IsNull
(Me.[End Date]) Then
Cancel = True
MsgBox "You must supply SSN, Start Date, and End
Date."
Else
strWhere = "([SSN] = """ & Me.SSN & """) AND (" _
& Format(Me.[Start Date], conJetDate) & _
" < [End Date]) AND ([Start Date] < " & _
Format(Me.[End Date], conJetDate & ")")
If Not Me.NewRecord Then
'Existing record does not clash with itself.
strWhere = strWhere & " AND ([ctrl nbr] <> " &
Me.[ctrl nbr] & ")"
End If
varResult = DLookup("[ctrl nbr]", "[LEAVES]",
strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with CTRL NBR " & varResult
End If
End If
End Sub

___________________________________________________________

Thank you.

-----Original Message-----
Cancel the BeforeUpdate event procedure of the form if this record would be
a duplicate.

Two records overlap if both these are true:
- A starts before B ends, and
- B starts before A ends.
Use that combined with the SSN to DLookup() the table to see if there is a
clash.

The following aircode assumes that all 3 fields are required, and that you
have a primary key named "ID" which is used to ensure that changes to an
existing record are not reported as a clash with itself.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.SSN) OR IsNull(Me.StartDate) OR IsNull (Me.EndDate) Then
Cancel = True
MsgBox "You must supply SSN, Start Date, and End Date."
Else
strWhere = "([SSN] = """ & Me.SSN & """) AND (" _
& Format(Me.[StartDate], conJetDate) & _
" < [EndDate]) AND ([StartDate] < " & _
Format(Me.[EndDate], conJetDate & ")"
If Not Me.NewRecord Then
'Existing record does not clash with itself.
strWhere = strWhere & " AND ([ID] <> " & Me.ID & ")"
End If
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clash with ID " & varResult
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


AJ Raiber said:
I need to see if it is possible in Access 2000 to create a
validation rule to prevent duplicate input of timeframes
based on name and the starting and end dates.

I have three fields affected by this, the SSN field which
autofills the name in a subform, the starting date and the
ending date. I need to prevent input of a group of dates
if they overlap with another group of dates for that SSN.

Is this possible and how can it be done if it is? Thank
you all!

AJ
Click to expand...


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