Please Help - Can't check for duplicates

**Albert D. Kallal** · Jun 29 '08, 03:15 AM

Re: Please Help - Can't check for duplicates

First, you subject says duplicates, and you are actually looking for
collisions. (grand canyon of different question here).

Furthermore, the ***big*** problem here is that supposedly you migrated this
application, but for two years, the collision code don't work? That just
does not seem right????

Anyway, there is little, if any reason why your code is not working after
migration to sql server. About 99%, or more of code should run untouched
when you move it to sql server.

>
In the access database I used VBA to check to ensure no duplicate
bookings. I wanted no dup events:
* on same day
* within the same time span
* in the same hall
* with a status of booked or tentative (other statuses include quoted -
duplicates are ok with that)
>
I'm still using access as the front end -- (Not an ADP but tables linked)
but for some reason this script doesn't work anymore.
>
Should I be using a trigger at the database level, or try to 'translate'
this VBA/SQL to work with SQL Server?

No, just get your old code working? Why re-invent the wheel. You can use a
query to test for collisions above.

To prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDat e <= EndDate
and
RequestEndDate >= StartDate

The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.

And, of course, you could simply add:

RequestStartDat e <= EndDate
and
RequestEndDate >= StartDate
and
RequestHall = Hall

Remember, if you make your date fields a date+ time, then the above will
even allow multiple days for a booking.

As for code....somethi ng like:

dim strWhere as string
dim dtRequeestStart Date as date
dim dtRequestEndDat e as date
dim intHall as integer

dtRequestStartD ate = inputbox("Enter start Date")
dtRequestEndDat e = inputbox("Enter end date")
intHall = inputbox("What room")

strWhere="#" & format(dtReques tStartDate,"mm/dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtReques tEndDate,"mm/dd/yyyy") & "# >= StartDate" &
_
" and hall = " & intHall

if dcount("*","tab leBooking",strW here) 0 then
msgbox "sorry, you can't book
....bla bla bla....

The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple conditions above does return ANY collisions.

However, it really begs the question, why not fix the code you already built
before?...it should be working just fine? I guess I can't understand how
something been running for two years that don't work??

Regardless, it is a fairly simply query to get collisions as the above
shows, it just up to you to provide a nice booking form that checks if the
requested date(s) + room is available.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

--

>
Thanks for your help
>
Lester
>
=============== =============== =============== =============== ==========
>
>
>
The Access VBA is:
>
>
>
>
>
Private Sub CheckConflict()
Dim db As Database
Dim rec As Recordset
Dim BookDate, StartTime, EndTime As Date
Dim Hall As Variant
Dim sqlstring, CurrentName As String
>
MsgBox "CheckConfl ict running"
>
BookDate = [Forms]![Hall Booking from Calendar].[ActiveXCtl30]
BookDate = Format(BookDate , "yyyy/mm/dd")
Hall = [Forms]![Hall Booking from Calendar].[Hall]
StartTime = [Forms]![Hall Booking from Calendar].[StartTime]
EndTime = [Forms]![Hall Booking from Calendar].[EndTime]
CurrentName = [Forms]![Hall Booking from Calendar].[EventName]
>
On Error GoTo EmptySet
Set db = CurrentDb
sqlstring = "SELECT Events.HallsID, Events.StartTim e, Events.EndTime,
Events.StatusID , Events.EventNam e, Events.Date, Halls.HallsID, Halls.[Hall
Name] FROM Halls INNER JOIN Events ON Halls.HallsID = Events.HallsID WHERE
((((Events.Stat usID)=2 Or (Events.StatusI D)=3))AND((((Ev ents.StartTime)
Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime & '#)
OR ((Events.EndTim e) Between #12/30/1899 ' & StartTime & '# And
#12/30/1899 ' & EndTime & '#)) AND ((Events.Date)= #' & BookDate & '# )
AND (Not(Events.Eve ntName= '" & EventName & "' ))AND((Halls.Ha llsID)= ' &
Hall & ' )));"
Set rec = db.OpenRecordse t(sqlstring, dbOpenDynaset, dbSeeChanges)
>
MsgBox ("This conflicts with the " & rec(4) & " booked in the " &
rec(7) & " Banquet Hall on " & rec(5) & " between " & rec(1) & " and " &
rec(2))
'If there are no conflicts, then allow the booking to proceed without
warning
>
Exit Sub
EmptySet:
>
' There are no conflicts
MsgBox "no event conflicts"
>
Exit Sub
>
rec.Close
End Sub

**Ed Murphy** · Jun 30 '08, 06:05 AM

Re: Please Help - Can't check for duplicates

Lester wrote:

Should I be using a trigger at the database level, or try to 'translate'
this VBA/SQL to work with SQL Server?

The former will be bulletproof against any front end whatsoever.

sqlstring = "SELECT Events.HallsID, Events.StartTim e, Events.EndTime,
Events.StatusID , Events.EventNam e, Events.Date, Halls.HallsID,
Halls.[Hall Name] FROM Halls INNER JOIN Events ON Halls.HallsID =
Events.HallsID WHERE ((((Events.Stat usID)=2 Or
(Events.StatusI D)=3))AND((((Ev ents.StartTime) Between #12/30/1899 ' &
StartTime & '# And #12/30/1899 ' & EndTime & '#) OR ((Events.EndTim e)
Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime &
'#)) AND ((Events.Date)= #' & BookDate & '# ) AND (Not(Events.Eve ntName=
'" & EventName & "' ))AND((Halls.Ha llsID)= ' & Hall & ' )));"

This definitely won't work without some fixing up, but should at least
be in the ballpark:

create trigger Events_IU on Events as
begin

declare @EventName varchar(30)
declare @HallName varchar(30)
declare @Date datetime
declare @StartTime datetime
declare @EndTime datetime

SELECT @EventName = e.EventName,
@HallName = h.[Hall Name],
@Date = e.Date,
@StartTime = e.StartTime,
@EndTime = e.EndTime
FROM Events e
INNER JOIN Halls h ON h.HallsID = e.HallsID
WHERE e.Date = inserted.Date
AND (e.StartTime between inserted.StartT ime and inserted.EndTim e
OR e.EndTime between inserted.StartT ime and inserted.EndTim e)
AND h.HallsID = inserted.HallsI D
AND e.StatusID in (2,3) -- booked, tentative
AND e.EventName <inserted.Event Name

if @EventName is not null
begin
raiserror('This conflicts with the %s booked in the %s Banquet Hall on
%s between %s and %s',
16, 1, e.EventName, h.[Hall Name], e.Date, e.StartTime, e.EndTime)
rollback transaction
end

end

Note that this will always generate a false positive if you try to
change the EventName of an existing row.

**Ed Murphy** · Jun 30 '08, 06:05 AM

Re: Please Help - Can't check for duplicates

Lester wrote:

Should I be using a trigger at the database level, or try to 'translate'
this VBA/SQL to work with SQL Server?

The former will be bulletproof against any front end whatsoever.

sqlstring = "SELECT Events.HallsID, Events.StartTim e, Events.EndTime,
Events.StatusID , Events.EventNam e, Events.Date, Halls.HallsID,
Halls.[Hall Name] FROM Halls INNER JOIN Events ON Halls.HallsID =
Events.HallsID WHERE ((((Events.Stat usID)=2 Or
(Events.StatusI D)=3))AND((((Ev ents.StartTime) Between #12/30/1899 ' &
StartTime & '# And #12/30/1899 ' & EndTime & '#) OR ((Events.EndTim e)
Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime &
'#)) AND ((Events.Date)= #' & BookDate & '# ) AND (Not(Events.Eve ntName=
'" & EventName & "' ))AND((Halls.Ha llsID)= ' & Hall & ' )));"

This definitely won't work without some fixing up, but should at least
be in the ballpark:

create trigger Events_IU on Events as
begin

declare @EventName varchar(30)
declare @HallName varchar(30)
declare @Date datetime
declare @StartTime datetime
declare @EndTime datetime

SELECT @EventName = e.EventName,
@HallName = h.[Hall Name],
@Date = e.Date,
@StartTime = e.StartTime,
@EndTime = e.EndTime
FROM Events e
INNER JOIN Halls h ON h.HallsID = e.HallsID
WHERE e.Date = inserted.Date
AND (e.StartTime between inserted.StartT ime and inserted.EndTim e
OR e.EndTime between inserted.StartT ime and inserted.EndTim e)
AND h.HallsID = inserted.HallsI D
AND e.StatusID in (2,3) -- booked, tentative
AND e.EventName <inserted.Event Name

if @EventName is not null
begin
raiserror('This conflicts with the %s booked in the %s Banquet Hall on
%s between %s and %s',
16, 1, @EventName, @HallName, @Date, @StartTime, @EndTime)
rollback transaction
end

end

Note that this will always generate a false positive if you try to
change the EventName of an existing row.

Please Help - Can't check for duplicates

Please Help - Can't check for duplicates

Comment

Comment

Comment