return records

[jaydeebe]

The following code creates a record set from a table and then takes a booking (two dates,start and end) from a form and compares to see if they overlap with any previous bookings. Currently it just returns a message if the booking overlaps (or writes a new record if no overlap) but i want to change it so that it returns the available resources for the dates entered. What should I be looking at to return records?

Private Sub Command4_Click()
Dim booking_rst As Recordset
Dim db As Database
Dim sql_string As String
Dim Start_time As Date
Dim Finish_Time As Date
Dim flag As Boolean

Set db = CurrentDb

sql_string = "SELECT tblbookings.* FROM tblbookings;"

Start_time = txtStart.Value
Finish_Time = txtEnd.Value
flag = False

Set booking_rst = db.OpenRecordset(sql_string)
If booking_rst.RecordCount > 0 Then
booking_rst.MoveLast
booking_rst.MoveFirst
While Not booking_rst.EOF
If Finish_Time > booking_rst!StartDate And Finish_Time < booking_rst!EndDate Then
MsgBox ("score1")
flag = True
MsgBox ("Resource is not available, try again")
booking_rst.MoveLast
Else
If Start_time >= booking_rst!StartDate And Start_time < booking_rst!EndDate Then
MsgBox ("score2")
flag = True
MsgBox ("Resource is not available, try again")
booking_rst.MoveLast
Else
If Start_time <= booking_rst!StartDate And Finish_Time >= booking_rst!EndDate Then
MsgBox ("score3")
flag = True
MsgBox ("Resource is not available, try again")
booking_rst.MoveLast
Else
If Start_time >= booking_rst!StartDate And Finish_Time <= booking_rst!EndDate Then
MsgBox ("score4")
flag = True
MsgBox ("Resource is not available, try again")
booking_rst.MoveLast
End If
End If
End If
End If
booking_rst.MoveNext
Wend
If flag = False Then
DoCmd.RunSQL "INSERT INTO tblbookings ([StartDate],[EndDate],[UserID],[ResID]) VALUES (txtStart,txtEnd,txtuserID,txtresID);"
MsgBox ("Booking confirmed")
Forms![frmbook]![Step1].SetFocus
End If
booking_rst.Close
End If

db.Close

End Sub


Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k