using beforeupdate event to prevent booking betweet start date and end date

[jahanara]

hey,
i am building a system for car hiring company. the tables are as follows

tbl: client details
tbl: Car Details
tbl: Hire

hire table contains the following fields
Client ID
Car ID
Hire ID
Start Date
End Date

i want to know when creating the form how do i build the beforeupdate expression so when i try booking a car that is ALREADY on hire for a client, is not rebooked untill the car is returned.

[MikeTheBike]

Hi

You could try something like this

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim LastStartDate As Date
    Dim LastEndDate As Date
    Dim InvalidDate As Boolean
    
    InvalidDate = False

    LastStartDate = DMax("[Start Date]", "Hire", "[Car ID] = " & Me.txtCarID)
    LastEndDate = DMax("[End Date]", "Hire", "[Car ID] = " & Me.txtCarID)
    
    If txtStartDate >= LastStartDate And txtStartDate <= LastEndDate Then Invalidate = True
    If txtEndDate >= LastStartDate And txtEndDate <= LastEndDate Then Invalidate = True
    
    If InvalidDate Then
        MsgBox "This car in on hire during the precified period!", vbExclamation, "Hire Date Error"
        Cancel = True
    ElseIf txtStartDate > txtEndDate Then
        MsgBox "The Start date connot be after the End date!", vbExclamation, "Hire Date Error"
        Cancel = True
    End If
    
    

End Sub

This assumes that the form has text box controls named txtCarID, txtStartDate and txtEndDate, and that [Car ID] is numeric.

Note: this is untried air code!

I would also strongly suggest youe remove the spaced contained in both your table and field names!?

HTH


MTB