Full Version: Scheduling Teams & Sites
UtterAccess Forums > Microsoft® Access > Access Date + Time
csatran
Hi
I am new to this group and to access. I am trying to make a database for a
NFP Youth Organization. We have mutiple home teams, visiting teams, and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)
If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub
I have also tried running the Query on its own but then can not add any more
events once I put it on the form. The Query (below) works on its own.
SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
FOr (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
Orderm new to this group and to access. I am trying to make a database for a
NFP Youth Organization. We have mutiple home teams, visiting teams, and
sites. I have the following in a before update event procedure. It isn't
working though and I am not sure why. Could someone please help me?
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
varResult = DLookup("ScheduleID", "Schedule", strWhere)
If ((Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) And _
(Me.SiteID = Me.SiteID.OldValue) And _
(Me.ActivityID = Me.ActivityID.OldValue)) And _
(Me.ActivityDate <> Me.ActivityDate.OldValue) Or
IsNull(Me.StartTime) Or IsNull(Me.EndTime) Or IsNull(Me.SiteID) Or
IsNull(Me.ActivityID) Or (Cancelled = True) Then
'do nothing
Else
If Not IsNull((Me.StartTime.OldValue >= Me.EndTime) Or _
(Me.EndTime.OldValue <= Me.StartTime) Or _
(Me.ActivityID <> Me.ActivityID.OldValue) Or _
(Me.SiteID <> Me.SiteID.OldValue)) Then
varResult = DLookup("ScheduleID", "Schedule", strWhere)
strMsg = "Clashes with Event # " & varResult & vbCrLf & "Continue
anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Double-booked")
<> vbYes Then
Cancel = True
End If
End If
End If
End Sub
I have also tried running the Query on its own but then can not add any more
events once I put it on the form. The Query (below) works on its own.
SELECT Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
FOr (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr1,
Nz((Schedule_1.StartTime>=[Schedule].[EndTime]) Or
(Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False) AS Expr2
FROM Schedule, Schedule AS Schedule_1
GROUP BY Schedule.ScheduleID, Schedule.ActivityDate, Schedule.StartTime,
Schedule.EndTime, Schedule.SiteID, Schedule.ActivityID,
Schedule.VisitingActivityID, Nz((Schedule_1.StartTime>=[Schedule].[EndTime])
Or (Schedule_1.EndTime<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>Schedule_1.SiteID) Or
([Schedule].[ActivityID]<>Schedule_1.ActivityID) Or
([Schedule].[ScheduleID]<>Schedule_1.ScheduleID),False)
HAVING (((Nz(([Schedule_1].[StartTime]>=[Schedule].[EndTime]) Or
([Schedule_1].[EndTime]<=[Schedule].[StartTime]) Or
([Schedule].[SiteID]<>[Schedule_1].[SiteID]) Or
([Schedule].[ActivityID]<>[Schedule_1].[ActivityID]) Or
([Schedule].[ScheduleID]<>[Schedule_1].[ScheduleID]),False))=True))
ORDER BY Schedule.StartTime;
Any help would be appreciated. I am doing this as a volunteer and to be
honest have spent days just trying to resolve this one issue.
Carol
ace
It's quite possible someone here can figure out what you're trying to do
from that.
think though that you'd be more likely to get an answer if you explained what
you are trying to do in plain words and posted the structure of the tables
involved.
HiTechCoach
Welcome to Utter Access.

Oagree with ace.

We can not run your code to see what the problem is without your tables and some data.

Without knowing what the code and query is trying to do, the best we can do is look for syntax errors. If the code/sql runs without errors, then it is a logic problem. Since we do not know the "logic" of what you are doing, I am not sure if any one will be able to help you with what you posted.
csatran
We have 5 divisions of teams with 1-3 teams at each division. For Example, Bantam A, PWA, PWB, Mite A, Mite B1, Mite B2.

We have 2 rinks (sites) and also go to opposing sites. We only need to maintain a schedule for our sites.

Yve 5 divisions of teams with 1-3 teams at each division. For Example, Bantam A, PWA, PWB, Mite A, Mite B1, Mite B2.

We have 2 rinks (sites) and also go to opposing sites. We only need to maintain a schedule for our sites.

YOur Assn plays other Assn teams, we don't need to maintain the other Assn team schedules.

For Example:
1)
1/1 Bantam A vs Opposing Bantam B @ Rink 1 9:30 - 11:00
1/2 Bantam A vs Opposing Bantam C @ Rink 1 9:30 - 11:00
This would be ok
2)
1/1 Bantam A vs Opposing Bantam B @ Rink 1 9:30 - 11:00
1/1 PW A vs Opposing PWB @ Rink 1 9:30 - 11:00
This would not be okay - Rink Times overlap
3)
1/1 Bantam A vs Opposing PW B @ Rink 1 9:30 - 11:00
This would not be okay - 2 differing divisions
4)
1/1 Bantam A vs Opposing Bantam B @ Rink 1 9:30 - 11:00
1/1 Bantam A vs Opposing Bantam B @ Rink 1 1:00 - 2:00
This would be okay
5)
1/1 Bantam A vs Opposing Bantam B @ Rink 1 9:30 - 11:00
1/1 Bantam A vs Opposing Bantam B @ Rink 1 1:00 - 2:00
1/1 Bantam A vs Opposing Bantam B @ Rink 1 3:00 - 4:00
This would not be okay - more than 2 games for a team in 1 day.
6)
1/1 Bantam A vs Opposing Bantam B @ Rink 1 9:30 - 11:00
1/1 Bantam A vs Opposing Bantam B @ Rink 2 10:30-12:00
This would not be okay - team times overlap

Oalso should try to make it so there is at least 1 hour between games. I then also need to take these schedules & put them over on an Event/Volunteer Schedule and Referee Schedule but at this point I would be happy just to get the team/site schedule figured out.

I will try to attach what I have.

Edited by: csatran on Tue Jan 17 17:46:40 EST 2006.
csatran
Attachment
ace
I'm still not sure what your task is.
Are you trying to generate a round-robin type schedule of
team matchups or do you receive a match list and are just
trying to schedule site and times for home matches?
Do you have a table that lists sites and the dates/times they are
available?
What are the rules?
no matches between divisions
one hour between matches
2 game limit per team per day (a required recuperation time?)
Your attached file was not much help in understanding the requirements.
csatran
I will need to manually enter the games but need to make sure I don't make a mistake. Kind of like a more complex appt schedule.
ates & times will be from Nov 1 to March 15. Games on weekends, practices during the week (divisions can overlap during practices).
We have 2 home sites and will travel to about 50 other sites. I don't have all the visitors sites in the db yet.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.