Open ended booking system Validation using VBA

[Armenante]

Hi,

I am creating a database for my A level project and i am stuck on the validation for the booking part of the database, and was just wondering if anyone could help me out? It is a plumbing booking and stock database in the booking section I have start date, start time and duration field. I want to add validation on the start date field as an after update event. Any ideas would be extremely helpful. Thank you.


Tom

[Armenante]

please help me i am really stuck and if i can not work out a good way to do it using open ended booking i will have to use booking slots and that is just the easy way out.


Tom

[ADezii]

Hi,

I am creating a database for my A level project and i am stuck on the validation for the booking part of the database, and was just wondering if anyone could help me out? It is a plumbing booking and stock database in the booking section I have start date, start time and duration field. I want to add validation on the start date field as an after update event. Any ideas would be extremely helpful. Thank you.


Tom

What is the Validation on the Start Date Field that you are looking for and is this Table, Form, or Field Validation?

[Armenante]

Its on the start date field in the form to stop double booking, i want something like this

1.
Private Sub ScheduleEndTime_AfterUpdate()
2.
Dim db As DAO.Database
3.
Dim rs As DAO.Recordset
4.
Dim strMessage As String
5.
Dim startTime As Date
6.
Dim endTime As Date
7.
Dim test As Boolean
8.

9.
test = False
10.
If Me.NewRecord = True Then
11.
Set db = CurrentDb
12.
Set rs = db.OpenRecordset("SELECT [ScheduleStartTime], [ScheduleEndTime] " & _
13.
"FROM Schedule INNER JOIN ScheduleDetails " & _
14.
"ON Schedule.ScheduleID=ScheduleDetails.ScheduleID " & _
15.
"WHERE [CourtID]=" & Forms![Bookings]![BookingsSubform].Form![CourtID] & " " & _
16.
"AND [ScheduleDate]=#" & Forms![Bookings]![BookingsSubform].Form![ScheduleDate] & "#")
17.

18.
startTime = Me.ScheduleStartTime
19.
endTime = Me.ScheduleEndTime
20.
If rs.RecordCount = 0 Then Exit Sub
21.
rs.MoveFirst
22.
Do Until rs.EOF
23.
If startTime = rs!ScheduleStartTime Or endTime = rs!ScheduleEndTime Then
24.
test = True
25.
ElseIf startTime > rs!ScheduleStartTime And startTime < rs!ScheduleEndTime Then
26.
test = True
27.
ElseIf endTime < rs!ScheduleEndTime And endTime > rs!ScheduleStartTime Then
28.
test = True
29.
End If
30.
If test Then
31.
rs.MoveLast
32.
End If
33.
rs.MoveNext
34.
Loop
35.

36.
If test = False Then
37.
MsgBox ("Time is available")
38.
Else
39.
MsgBox ("Time is unavailable")
40.
Me.ScheduleStartTime = Null
41.
Me.ScheduleEndTime = Null
42.
End If
43.

44.
rs.Close
45.
Set rs = Nothing
46.
Set db = Nothing
47.

48.
End If
49.

50.
End Sub

from this http://www.thescripts.com/forum/thread604404.html thread but i am not sure how to implement it so it works on mine.

Tom

[Armenante]

Hi guys erm i tried to make that code above work in my database but it did not work out very well could anyone tell me what i have done wrong please. By the way it is in the form job* table which is based on the table of the same name.

Private Sub Start_date_AfterUpdate()
 
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean
 
test = False
If Me.NewRecord = True Then
Set db = CurrentDb
     Set rs = db.OpenRecordset("SELECT [Start_time], [End] " & _
             "FROM Forms![Job* table] " & _
             "ON Forms![job* table].Job_ID " & _
             "WHERE [Job_ID]=" & Forms![Job* table]![Booking] & " " & _
             "AND [Start_Date]=#" & Forms![Job* table].Form![Start_date] & "#")
 
startTime = Me.Start_time
endTime = Me.End
rs.MoveFirst
     Do Until rs.EOF
         If startTime = rs!Start_time Or endTime = rs!End Then
           test = True
         ElseIf Start_time > rs!Start_time And startTime < rs!End Then
           test = True
          ElseIf endTime < rs!End And endTime > rs!Start_time Then
 
 test = True
 
        End If
 
        If test Then
 
          rs.MoveLast
 
        End If
 
        rs.MoveNext
 
    Loop
 
     If test = False Then
       MsgBox ("Time is available")
     Else
       MsgBox ("Time is unavailable")
       Me.Start_time = Null
       Me.End = Null
     End If
 
     rs.Close
     Set rs = Nothing
     Set db = Nothing
 
   End If
 
 End Sub
 
 

Tom

[ADezii]

Hi guys erm i tried to make that code above work in my database but it did not work out very well could anyone tell me what i have done wrong please. By the way it is in the form job* table which is based on the table of the same name.

Private Sub Start_date_AfterUpdate()
 
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMessage As String
Dim startTime As Date
Dim endTime As Date
Dim test As Boolean
 
test = False
If Me.NewRecord = True Then
Set db = CurrentDb
     Set rs = db.OpenRecordset("SELECT [Start_time], [End] " & _
             "FROM Forms![Job* table] " & _
             "ON Forms![job* table].Job_ID " & _
             "WHERE [Job_ID]=" & Forms![Job* table]![Booking] & " " & _
             "AND [Start_Date]=#" & Forms![Job* table].Form![Start_date] & "#")
 
startTime = Me.Start_time
endTime = Me.End
rs.MoveFirst
     Do Until rs.EOF
         If startTime = rs!Start_time Or endTime = rs!End Then
           test = True
         ElseIf Start_time > rs!Start_time And startTime < rs!End Then
           test = True
          ElseIf endTime < rs!End And endTime > rs!Start_time Then
 
 test = True
 
        End If
 
        If test Then
 
          rs.MoveLast
 
        End If
 
        rs.MoveNext
 
    Loop
 
     If test = False Then
       MsgBox ("Time is available")
     Else
       MsgBox ("Time is unavailable")
       Me.Start_time = Null
       Me.End = Null
     End If
 
     rs.Close
     Set rs = Nothing
     Set db = Nothing
 
   End If
 
 End Sub
 
 

Tom

If test evaluates to True in Lines 25, 27, or 30, a MoveLast was executed in Line #36 and then a MoveNext in Line #40 which will generate an Error. This could happen on the 1st Record in the Recordset before spanning the entire Recordset.