Exclude current record of a select

  • Thread starter Thread starter pmeyssonnier
  • Start date Start date
P

pmeyssonnier

I would like write a VBA function to check before update the validity
period of a record

The validity period is succeed

if Start date is less than End Date

If the new validity period [start_date, end date] doesn't overlap an
old one.[START_DATE, END_DATE ]

I have already write the function and procedure, but in the case of
insert the function "check_alloc_am" works perfectly but If I update
the validity period of an existing record, I cannot find the way to
exclude of the SELECT the current updated record. I hope you understand
what I say.

NB: I have created a separate function to check the validity period
because I would like insert/update record manually through a form or
insert a group of record from table

e.g OPERATOR_ID = 1
insert #01/01/2005# - #12/31/2005# ==> OK
insert #06/01/2006# - #01/31/2006# ==> NOK Start date is greater
than End Date
insert #01/01/2006# - #12/31/2006# ==> OK
update #01/01/2006# - #07/31/2006# ==> NOK because the validity
period overlaps an existing record but in this case the existing record
is the current updated record ==> OK

Thanks in advance.

Regards


Here are my procedures and function
'=============================================
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As Recordset

'Open the RecordsetClone of the form
Set rst = Me.RecordsetClone

With rst
.AddNew
!OPERATOR_ID = me!OPERATOR_ID
!OPERATOR_START_DATE = me!OPERATOR_START_DATE
!OPERATOR_END_DATE = me!OPERATOR_END_DATE
!AM_ID = me!AM_ID
End With

If check_alloc_am_transit(rst, True) <> "No Error" Then
Cancel = True
SendKeys "{ESC}"
End If

End Sub

'===============================================================
Function check_alloc_am_transit(rst As Recordset, ShowErr As Boolean)
As String

Dim check As Recordset
Dim strsql, strErrMsg As String

ErrMsg = "No Error"

If rst!OPERATOR_START_DATE > rst!OPERATOR_AM_END_DATE Then
strErrMsg = "Start date is greater than end date !"
GoTo Err_check
End If

'Search an overlap validity period with the same network and Role
strsql = "SELECT * FROM OPERATOR_AM_TRANSIT " & _
"WHERE (OPERATOR_START_DATE <= #" & _
Format(rst!OPERATOR_END_DATE, "mm/dd/yyyy") & "#) " &
_
"AND (OPERATOR_END_DATE >#" & _
Format(rst!OPERATOR_START_DATE, "mm/dd/yyyy") & "#) "
& _
"AND (OPERATOR_ID = " & rst!OPERATOR_ID & ") "

Set check = CurrentDb().OpenRecordset(strsql)

'How can I exclude the current record if it's an updated record ??

If Not check.EOF Then
strErrMsg = "Overlap of the validity period!"
GoTo Err_check
End If

Exit_check:
check_alloc_am_transit = strErrMsg
ckeck.close
set check = nothing
Exit Function

Err_check:
If ShowErr Then
MsgBox strErrMsg, vbCritical
End If
GoTo Exit_check

End Function

'====================================================
Sub Insert_alloc_am_transit(rst As Recordset)

Dim alloc As Recordset
Dim strsql As String

strsql = "SELECT * FROM DWHCBU_COMM_DB_OPERATOR_AM_TRANSIT " & _
"WHERE (OPERATOR_ID = " & rst!OPERATOR_ID & ") " & _
"AND (AM_ID = '" & rst!AM_ID & "') " & _
"AND ((OPERATOR_END_DATE + 1 = #" & rst!OPERATOR_START_DATE
& "#) " & _
"OR (OPERATOR_START_DATE - 1 = #" & rst!OPERATOR_END_DATE
& "#))"

Set alloc = CurrentDb().OpenRecordset(strsql)

'Test if the last period of validity is consecutive to the new one
With alloc
If Not .EOF Then
If !OPERATOR_END_DATE + 1 = rst!OPERATOR_START_DATE Then
'then extend the last period of validity
.Edit
!OPERATOR_END_DATE = rst!OPERATOR_END_DATE
.Update
ElseIf !OPERATOR_AM_START_DATE - 1 = rst!OPERATOR_AM_END_DATE Then
'then extend the last period of validity
.Edit
!OPERATOR_START_DATE = rst!OPERATOR_START_DATE
.Update
End If
Else
'else insert a new row
.AddNew
!OPERATOR_ID = rst!OPERATOR_ID
!OPERATOR_START_DATE = rst!OPERATOR_START_DATE
!OPERATOR_END_DATE = rst!OPERATOR_END_DATE
!AM_ID = rst!AM_ID
.Update
End If
.Close
end with

End Sub
 
When doing a modification, amend the SQL to include the current record's
unique ID field and filter that from the results. You'll need 2 different
SQL statements, one for a new record and one for a modified record.

To eliminate the current record, add the following to the WHERE clause of
the SQL.

"AND (OPERATOR_ID = " & rst!OPERATOR_ID & ") And [IDField] <> " &
rst!IDField

This syntax assumes the IDField to be a number data type. If not, adjust the
syntax accordingly.

--
Wayne Morgan
MS Access MVP


I would like write a VBA function to check before update the validity
period of a record

The validity period is succeed

if Start date is less than End Date

If the new validity period [start_date, end date] doesn't overlap an
old one.[START_DATE, END_DATE ]

I have already write the function and procedure, but in the case of
insert the function "check_alloc_am" works perfectly but If I update
the validity period of an existing record, I cannot find the way to
exclude of the SELECT the current updated record. I hope you understand
what I say.

NB: I have created a separate function to check the validity period
because I would like insert/update record manually through a form or
insert a group of record from table

e.g OPERATOR_ID = 1
insert #01/01/2005# - #12/31/2005# ==> OK
insert #06/01/2006# - #01/31/2006# ==> NOK Start date is greater
than End Date
insert #01/01/2006# - #12/31/2006# ==> OK
update #01/01/2006# - #07/31/2006# ==> NOK because the validity
period overlaps an existing record but in this case the existing record
is the current updated record ==> OK

Thanks in advance.

Regards


Here are my procedures and function
'=============================================
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rst As Recordset

'Open the RecordsetClone of the form
Set rst = Me.RecordsetClone

With rst
.AddNew
!OPERATOR_ID = me!OPERATOR_ID
!OPERATOR_START_DATE = me!OPERATOR_START_DATE
!OPERATOR_END_DATE = me!OPERATOR_END_DATE
!AM_ID = me!AM_ID
End With

If check_alloc_am_transit(rst, True) <> "No Error" Then
Cancel = True
SendKeys "{ESC}"
End If

End Sub

'===============================================================
Function check_alloc_am_transit(rst As Recordset, ShowErr As Boolean)
As String

Dim check As Recordset
Dim strsql, strErrMsg As String

ErrMsg = "No Error"

If rst!OPERATOR_START_DATE > rst!OPERATOR_AM_END_DATE Then
strErrMsg = "Start date is greater than end date !"
GoTo Err_check
End If

'Search an overlap validity period with the same network and Role
strsql = "SELECT * FROM OPERATOR_AM_TRANSIT " & _
"WHERE (OPERATOR_START_DATE <= #" & _
Format(rst!OPERATOR_END_DATE, "mm/dd/yyyy") & "#) " &
_
"AND (OPERATOR_END_DATE >#" & _
Format(rst!OPERATOR_START_DATE, "mm/dd/yyyy") & "#) "
& _
"AND (OPERATOR_ID = " & rst!OPERATOR_ID & ") "

Set check = CurrentDb().OpenRecordset(strsql)

'How can I exclude the current record if it's an updated record ??

If Not check.EOF Then
strErrMsg = "Overlap of the validity period!"
GoTo Err_check
End If

Exit_check:
check_alloc_am_transit = strErrMsg
ckeck.close
set check = nothing
Exit Function

Err_check:
If ShowErr Then
MsgBox strErrMsg, vbCritical
End If
GoTo Exit_check

End Function

'====================================================
Sub Insert_alloc_am_transit(rst As Recordset)

Dim alloc As Recordset
Dim strsql As String

strsql = "SELECT * FROM DWHCBU_COMM_DB_OPERATOR_AM_TRANSIT " & _
"WHERE (OPERATOR_ID = " & rst!OPERATOR_ID & ") " & _
"AND (AM_ID = '" & rst!AM_ID & "') " & _
"AND ((OPERATOR_END_DATE + 1 = #" & rst!OPERATOR_START_DATE
& "#) " & _
"OR (OPERATOR_START_DATE - 1 = #" & rst!OPERATOR_END_DATE
& "#))"

Set alloc = CurrentDb().OpenRecordset(strsql)

'Test if the last period of validity is consecutive to the new one
With alloc
If Not .EOF Then
If !OPERATOR_END_DATE + 1 = rst!OPERATOR_START_DATE Then
'then extend the last period of validity
.Edit
!OPERATOR_END_DATE = rst!OPERATOR_END_DATE
.Update
ElseIf !OPERATOR_AM_START_DATE - 1 = rst!OPERATOR_AM_END_DATE Then
'then extend the last period of validity
.Edit
!OPERATOR_START_DATE = rst!OPERATOR_START_DATE
.Update
End If
Else
'else insert a new row
.AddNew
!OPERATOR_ID = rst!OPERATOR_ID
!OPERATOR_START_DATE = rst!OPERATOR_START_DATE
!OPERATOR_END_DATE = rst!OPERATOR_END_DATE
!AM_ID = rst!AM_ID
.Update
End If
.Close
end with

End Sub
Click to expand...
 
Thanks Morgan,

But there is no current record's unique field ID in the table.

It's a TERADATA table attached in the Ms-Access front end.Do you now if
there is the Teradata equivalent of the pseudocolumn Oracle ROWID, to
return the current record ?


Pierre

Pieree
 
No, I do not, sorry. Is there no field or combination of fields in the table
that make up a unique value for each record?
 
Thanks you for your advive,

but I' m not the table owner. In the meantine, I modified my function
to pass the old values of start_date and end_date.(in edit)

If check_alloc_am_transit(rst, True,start_date.old, end_date.old) <>
"No Error" Then
Cancel = True
SendKeys "{ESC}"
End If

......

WHERE (OPERATOR_START_DATE <= #" & format(rst!OPERATOR_END_DATE,
"mm/dd/yyyy") & "#) " & _
AND (OPERATOR_END_DATE >#" & format(rst!OPERATOR_START_DATE,
"mm/dd/yyyy") & "#) " & _
AND (OPERATOR_ID = " & rst!OPERATOR_ID & ") " & _
AND (OPERATOR_START_DATE <> #" &
format(rst!OPERATOR_END_DATE.old, "mm/dd/yyyy") & "#) " & _
AND (OPERATOR_END_DATE < > #"
&Format(rst!OPERATOR_START_DATE.old, "mm/dd/yyyy") & "#) "
 
Back
Top