Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    10

    Unanswered: appointments clashes and dlookup problems

    Hi all,

    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.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That all depends on how you structured your database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could trye something like this:
    Code:
    Option Compare Database
    Option Explicit
    
    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)
        rst.FindFirst strCriteria
        If rst.NoMatch Then VehicleFree = True
        rst.Close
        Set rst = Nothing
        
    End Function
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •