Find and Delete existing records

  • Thread starter Thread starter hughess7
  • Start date Start date
H

hughess7

Hi all

I have some code which creates dates in a related table, snippet below:

NoOfDays = Forms![frm Itinerary]![Itinerary].Form![ReviewDays] - 1
StartDate = Forms![frm Itinerary]![Itinerary].Form![ReviewDate]
ItinID = Forms![frm Itinerary]![Itinerary].Form![ItineraryID]

dtmDate = StartDate

For n = 1 To NoOfDays
dtmDate = DateAdd("d", 1, dtmDate)

strSQL = "INSERT INTO [Itinerary Dates]([ItineraryID],[ReviewDates])" & _
"VALUES(" & ItinID & ", #" & Format(dtmDate, "mm/dd/yyyy") & "#)"

It takes the ItineraryID and StartDate and increments the dates based on
NoOfDays. This works well, but if the number of days changes, I would like to
check to see if any date records already exist and prompt user to delete them
if so.

What is the best way to do this?

Thanks, Sue
 
Hi Sue

First create a filter string that selects the target records:

strFilter = "(ItineraryID=" & ItinID & ") and (ReviewDates between " _
& SqlDate(dtmStart) & " and " & SqlDate(dtmEnd) & ")"

[see below for the SqlDate function]

Now you can use this filter to find and/or delete the records:

iCount = DCount( "*", "[Itinerary Dates]", strFilter)
If iCount > 0 then
<message to confirm if required>
CurrentDb.Execute "Delete from [Itinerary Dates] where " _
& strFilter, dbFailOnError
End If

This function formats a date or date+time into an international format for
use in a SQL statement:

Public Function SqlDate(ByVal d As Variant) As String
Dim sFormat As String
On Error Resume Next
d = CDate(d)
If Err = 0 Then
If TimeValue(d) = 0 Then
sFormat = "\#yyyy\-mm\-dd\#"
Else
sFormat = "\#yyyy\-mm\-dd hh\:nn\:ss\#"
End If
SqlDate = Format(d, sFormat)
Else
Err.Clear
End If
End Function
 
Back
Top