Need more help on preventing duplicates

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

AJ Raiber

This was posted originally yesterday (9-22-03). Allen
responded last night and yet I still have problems as
listed below. Since this has burried a few pages deep I
wanted to repost it to try and figure out this working
issue. Thank you.

__________________________________________________________

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...
..
 
I finally got it to work continuously. This is the final
script. My field names are CTRL NBR (The Primary Key),
SSN, Start Date and End Date. This now works beautifully
to ensure that no SSN has periods of dates on this form
that overlap with another set already input for that SSN.

The code is as follows:

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


-----Original Message-----
This was posted originally yesterday (9-22-03). Allen
responded last night and yet I still have problems as
listed below. Since this has burried a few pages deep I
wanted to repost it to try and figure out this working
issue. Thank you.

__________________________________________________________

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