Find and Delete existing records

[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

 

[Graham Mandeno]

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