By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,473 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,473 IT Pros & Developers. It's quick & easy.

prevent bookin while the car is on hire

P: 3
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.
Mar 3 '10 #1
Share this Question
Share on Google+
3 Replies

P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Dim varHire As Variant     'declare a variable for the hire ID. I used variant because I'm not sure what data type HireID is.
  2. Dim strHire As Variant     'declare a variable for the return value of DLookup
  4. varHire = txtHire     'this assumes that you get the hire ID from a field on a form
  6. 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.
  8. if Not IsNull(strHire) then
  9.      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
  10.      Me.HireID = Null     'set the hire field to null
  11.      Me.HireID.SetFocus     'put the cursor back in the hire field so the user can select another vehicle
  12.      Exit Sub     'exit the sub so that the user can re-enter an hire ID
  13. end if
Mar 3 '10 #2

P: 3
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?
Mar 4 '10 #3

Expert Mod 15k+
P: 31,768
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.
Mar 4 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.