prevent bookin while the car is on hire

[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.

[beacon]

Is the return of the car indicated by the End Date field? If so, you could use DLookup to see if the field is null or not and throw an error message if it is. I'm not sure I would use BeforeUpdate though, but that's mostly just personal preference. Also, I would remove the spaces in your field names...for instance, I would change End Date to EndDate.

Here's an example of DLookup:

Dim varHire As Variant     'declare a variable for the hire ID. I used variant because I'm not sure what data type HireID is.
Dim strHire As Variant     'declare a variable for the return value of DLookup

varHire = txtHire     'this assumes that you get the hire ID from a field on a form

strHire = DLookup("EndDate", "tblHire", "HireID = '" & varHire & "'")   'EndDate is the field you're looking up, tblHire is the table you look in, and HireID is the lookup value to compare against.

if Not IsNull(strHire) then
     MsgBox "The vehicle you are trying to book has already been allocated", vbExclamation, "Error - Booked Vehicle"     'inform the user that the vehicle has already been booked
     Me.HireID = Null     'set the hire field to null
     Me.HireID.SetFocus     'put the cursor back in the hire field so the user can select another vehicle
     Exit Sub     'exit the sub so that the user can re-enter an hire ID
end if

[jahanara]

hey thanks for the reply, i tried using the above but it still dont work.. is it possible i can do it as a query?

[NeoPa]

If you post the SQL you're currently using for your form we can tell you how to amend it to filter out those items currently on hire.