show only cars available… (Access 2000)

Topic

New Reply

Hi, I am new to access. I have a problem.
I am working on a db to keep track of vehicles hired.
I want to show a list of only the cars that have not been booked on a certain date.

Overview:

For each new main date record I record the clients details & needs etc.
In a subform within this main form I want to allow the user to pick the cars he wants to book. The details are kept in a table (carsbooked)
I need to show a list of cars available only on that date so they can be recorded as being booked. I don’t want to show any vehicles which are already booked for the date record in my dropdown menu for booking cars??
The cars are kept in a seperate table called “cars”. This is the list of the fleet of vehicles.

If you follow me, can anyone give me some idea as to how I could acheive this.

Reply | Quote

Replies

What does a record in your CarsBooked table look like? I assume it contains at least something like CustomerID and VehicleID. But does it have one date field (a car is booked if there is a record for a certain date) or two (car is booked from Date1 through Date2)?

Reply | Quote

Carsbooked table as follows:

bookedref – primary
date – date is from main form.
carid –
ref – individual record ref from main form

Reply | Quote

I’m also working on something to the same effect.
I’m re-hashing a Bodyshop management system, one of my modules is for handling courtesy cars.
I’ts quite complex to say the least.
Sounds to me like you need to start looking at a query system wher the car is Active/NotActive – BookedOut/NotBookedOut.
If your project is not too big, post it to me and I’ll try to help you along with it, tips advice etc.

Regards
Dave

Reply | Quote

From your reply, I gather that there is an individual record in CarsBooked for each day a car is booked. In that case, I would create two queries as follows.

First, create a query that returns the CarID of all cars that are booked for the date selected in the main form. Its SQL could look like

SELECT DISTINCT CarID
FROM CarsBooked
WHERE Date = [Forms]![frmMainForm]![txtBookDate]

Replace frmMainForm and txtBookDate by the appropriate names. I’ll name this query qryCarsBooked.
(Note: it may be too late to change now, but in general, it is not advisable to name a field “Date”, because it can cause confusion with the VBA function Date. It’s better to use a name like “BookDate”.)

Next, create a query that returns the CarID of all cars that have NOT been booked for the date selected in the main form. One possibility is to base this query on the Cars table and the qryCarsBooked query, with an outer join:

SELECT Cars.CarID
FROM Cars LEFT JOIN qryCarsBooked ON Cars.CarID = qryCarsBooked.CarID
WHERE qryCarsBooked.CarID Is Null

This query is not updateable. That is not a problem if you use it as row source for a list box or combo box. If you want it to be updateable, an alternative is a nested query based on the Cars table:

SELECT CarID
FROM tblCars
WHERE CarID Not In (SELECT CarID FROM qryCarsBooked)

Reply | Quote

Mik

Just a few tips for the stumbling bricks I came across with my Fleet/Hire db.

I tend to use batch No.s as an ID. Each time a fleet is comissioned, it will have the same batch No. This is very useful for later purposes (Vehicle Supplier) , (Batch de-Commision) , (reporting) etc etc.

Another useful tip is put a yes/no field in the table. If a vehicle is written off or sold for some reason, you can set the vehicle Active or None Active. your display window or form can then be set to only show vehicles which are currently Active or not.
Theirs nothing worse than over populating with vehicles you do not use.

I’ve attached a pic which may give you some idea’s

Have Fun

Dave
If you need any help, let me know.

Regards

Dave

Reply | Quote

Thanks for the info guys. I am sorry, I haven’t been around recently to respond to anyone.
Back in country now and will get down to work on finishing this project. I will respond accordingly when I get something working.
Hans I think your queries may be the key to my problem. I will experiment with all suggestions anyway.

Thanks again for ideas, examples and help.

Reply | Quote

Take a look at this site which offers a very elegant solution to your problem. Although it is a doctor’s appointment system, it is essentially the same idea, ie, to avoid booking clashes. This solution provides a drop down list displaying only those appointments which are free.

http://www.advanced-ict.info/databases/clash.html%5B/url%5D Edited by Wendell to activate link 09-Oct-02 17:34 MDT (UTC-7)

Rob

Reply | Quote