I'm driving myself crazy with a problem in trying to translate a query
written for Access to that for SQL server. I would think that I would
use a trigger, but am not sure how to set it up.
We have a database that manages bookings in four banquet halls. It was
running in an Access database, but two years ago, I migrated 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?
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
|
