Form: Broadcast Schedule not finding over write (1 Viewer)

[MissCLuvr]

I have a form which if a time to start a program is typed in (the programs has a table that tells the length of the programs) it automatically calculates the end time and the next row has the new time to start. Well, here's the problem if a time is entered that overlaps with the new time I don't know how to tell the program to find that problem and notify the user. Here is an example:


Date Start Time Length End Time Prog Title
01/05 1:00:00 15:00 1:15:00 xxxxxx
01/05 1:15:00 15:00 1:30:00 XXXXXX
01/05 1:30:00 15:00 1:45:00 xtstls
01/05 1:35:00 15:00 1:50:00 tslkto

It does not notice that 1:35 overlaps with the 1:45 program because it is programmed to automatically put the end time of the last program as the start time for the next program. So the manual entry could be an overwrite and the program will not notice it.

Can anyone help me with a suggestion.

MissC

 

[D-Fresh]

Try to run a SQL validation on your table in the BeforeUpdate event of the "Start Time" on your form. Use this code...

Private Sub StartTime_BeforeUpdate(Cancel As Integer)

Dim MyDB as database
Dim MyRecs as recordset
Dim MySQL as string

MySQL = "SELECT * FROM [Your_Table] WHERE [Start_Time]<#" & me![Form_Time_Field] & "# AND [End_Time]>#" & me![Form_Time_Field] & "#"

set MyDB = currentdb
set MyRecs = mydb.openrecordset(MySQL)

If not myrecs.eof then
msgbox("Sorry, that overlaps with " & myrecs![Title])
cancel = true
end if

'Clean up your objects
mydb.close
set MyDB = nothing
set MyRecs = nothing

end sub

This will return an empty set if your form time is not within any existing time. SO if the set is not empty, you know that there is a conflict and you can cancel the update. Hope that helps you out.

Doug

 

[MissCLuvr]

Thanks for the suggestion. Now I have another problem. I have an error on the On Open:

Private Sub Form_Open(Cancel As Integer)
CheckBroadcastDate

End Sub

is not picking up. What could be wrong.

MissC