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:
RequestStartDate <= 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:
RequestStartDate <= 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....something like:
dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date
dim intHall as integer
dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")
intHall = inputbox("What room")
strWhere="#" & format(dtRequestStartDate,"mm/dd/yyyy") & "# <= EndDate" &
_
" and #" & format(dtRequestEndDate,"mm/dd/yyyy") & "# >= StartDate"
&
_
" and hall = " & intHall
if dcount("*","tableBooking",strWhere) > 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
pleaseNOOSpamKallal@[EMAIL PROTECTED]
>
> 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 "CheckConflict 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.StartTime, Events.EndTime,
> Events.StatusID, Events.EventName, Events.Date, Halls.HallsID,
Halls.[Hall
> Name] FROM Halls INNER JOIN Events ON Halls.HallsID = Events.HallsID
WHERE
> ((((Events.StatusID)=2 Or (Events.StatusID)=3))AND((((Events.StartTime)
> Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime & '#)
> OR ((Events.EndTime) Between #12/30/1899 ' & StartTime & '# And
> #12/30/1899 ' & EndTime & '#)) AND ((Events.Date)= #' & BookDate & '# )
> AND (Not(Events.EventName= '" & EventName & "' ))AND((Halls.HallsID)= '
&
> Hall & ' )));"
> Set rec = db.OpenRecordset(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
|
