Unanswered: appointments clashes and dlookup problems
I am a novice when it comes to VB but have been trying to solve this problems for a week and got nowhere quickly.
Can someone please help????
I am in the midst of designing a a basic a chauffeur company database.
What i would like is when the user enters in the dateofbooking, timeofcollection, durationofjob and vehicleID, the form automatically looks at the qsessions query to see if their are clashes with the vehicle being used on the day and time specified. I have formatted the query to produce general dates ie. 21/02/2010 18:00:00 on both enddate and startdate. Is this possible?
I know i either have to use dlookup or dcount on the before update event, but i just cant get it right. can someone make a suggestion of the type of syntax i need to use.
Option Compare Database
Function VehicleFree(ByVal VehicleID As Long, ByVal StartTime As Date, ByVal StopTime As Date) As Boolean
' Usage: Debug.Print VehicleFree(4, "9/14/2008 10:45", "9/16/2008 16:30")
' or: Debug.Print VehicleFree(4, #9/14/2008 10:45#, #9/16/2008 16:30#)
' on a form: Debug.Print VehicleFree(4, Me.StartTime.Value, Me.StopTime.Value)
' The structure of the VehiclesInUse table is like this:
' Field Name | Data Type
' VehicleID | Number (Long Integer)
' StartTime | Date/Time
' EndTime | Date/Time
Dim strSQL As String
Dim strCriteria As String
Dim rst As DAO.Recordset
strSQL = "SELECT VehiclesInUse.vehicleID FROM VehiclesInUse " & _
"WHERE (VehiclesInUse.StartTime>#" & Format(StartTime, "mm/dd/yyyy hh:nn") & "#) AND " & _
"(VehiclesInUse.EndTime<#" & Format(StopTime, "mm/dd/yyyy hh:nn") & "#)"
' If the VehicleID column in the VehiclesInUse table is of Text type, change for:
' strCriteria = "VehicleID = '" & VehicleID & "'"
strCriteria = "VehicleID = " & VehicleID
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.NoMatch Then VehicleFree = True
Set rst = Nothing