Update Query? *Headache*

[pandaking]

Hi there everyone, new here but after reading around it seems like I might hang about - so many helpful threads!

I have a slight problem. This flying club near me has employed me to make them a database.
I got into it because I know one of the guys who works there, and he mentioned they need one and I did Computing A level a few years back so I thought it would be a doddle and some much needed cash!

Well it's a headache :(

It's essentially a booking system. The problem is each flight you book has a start time and an end time which you can pick.
I have it set out on a form and it's working great, up to this point which is where you need to select a plane.

I am dealing with two types of planes - a Cessna and a piper. So after picking a date for the booking and the start and end times you select the type of plane in a list box.

I then have another listbox which I want to show which planes under that category (piper or Cessna) are not booked on the selected date and between the selected times.

So that's what I would like. Now the rest of this is me failing to manage it.

My idea was to put a flag in the table of planes, and then run an update query on the flag to mark planes which were available.

So I am using my bookings table, and the list of planes table to run this query.

In my head, I see the criteria as :
• Booked date is equal to selected date
• Plane type is equal to selected plane type
• Flag is to be updated to "yes"
• BookedStartTime is greater than EndTime AND BookedEndTime is less than StartTime.

In my head that should work. The last bullet point will only select planes which are not already booked out at the selected times, the top bullet point will make sure I am only looking at bookings on the right day etc.

The problem is when it runs it updates the wrong things. It kind of half works.
The biggest problem is that it is also not showing planes which have no bookings that day, only planes which have bookings that day, but ones which are free at the selected time.

I am not sure how I would type the last bullet point in.

SQL View of it:
UPDATE tblPlane INNER JOIN tblBookings ON tblPlane.PlaneID = tblBookings.PlaneID SET tblPlane.Flag = Yes
WHERE (((tblBookings.[Time Start])>=[Forms]![frmMakeBooking]![cboEndTime]) AND ((tblBookings.[Time Finish])>=[Forms]![frmMakeBooking]![cboStartTime]) AND ((tblBookings.Date)=[Forms]![frmMakeBooking]![txtSelectedDate]));


Any help, guidance or just a pointer in the right direction would be really gratefully received.
Thanks.

[MMcCarthy]

First forget about the UPDATE query. You can do this with cascading combo boxes. More details on this can be found in this tutorial.

Cascading Combo/List Boxes

Now we just need to work out the Row Source for the second combobox. The following query should only return Planes available during the booking time and date.

SELECT tblPlane.* 
FROM tblPlane
WHERE PlaneID NOT IN
(SELECT PlaneID FROM tblBookings
WHERE (((([Time Start])<=[Forms]![frmMakeBooking]![cboStartTime]
AND [Time Finish])>=[Forms]![frmMakeBooking]![cboStartTime]))
OR (([Time Start])<=[Forms]![frmMakeBooking]![cboEndTime]
AND [Time Finish])>=[Forms]![frmMakeBooking]![cboEndTime])))
AND (tblBookings.Date)=[Forms]![frmMakeBooking]![txtSelectedDate])));
 

Mary

[pandaking]

Wow Mary - thanks for the speedy and very helpful response!
I am trying to work out how to do the combo boxes now, but when I enter that code into the sql view of a query it says:

"Extra ) in query expression 'PlaneID NOT IN ..............' "

I have had a look through it myself to see what it is talking about but to be honest I have no idea what I am doing :p

Thanks again for the fantastic reply.

[MMcCarthy]

OK, try this ...

SELECT tblPlane.* 
FROM tblPlane
WHERE PlaneID NOT IN
(SELECT PlaneID FROM tblBookings
WHERE (((([Time Start]<=[Forms]![frmMakeBooking]![cboStartTime]
AND [Time Finish]>=[Forms]![frmMakeBooking]![cboStartTime]))
OR (([Time Start]<=[Forms]![frmMakeBooking]![cboEndTime]
AND [Time Finish]>=[Forms]![frmMakeBooking]![cboEndTime])))
AND (tblBookings.Date=[Forms]![frmMakeBooking]![txtSelectedDate])));
 

By the way you may have to add a criteria to this. Something like

AND (tblBookings.Date=[Forms]![frmMakeBooking]![txtSelectedDate])
AND (PlaneType=[Forms]![frmMakeBooking]![comboboxName])));

Mary

[pandaking]

Sorry I am not quite following where the criteria go, just in any of the criteria boxes of the query? Or a certain one?

Do I just switch the query I made with that main block of code to design view and paste the criteria code in that?

Thanks again for your super support :)

[MMcCarthy]

This would actually go in the Row Source for your second combo box.

SELECT tblPlane.* 
FROM tblPlane
WHERE PlaneID NOT IN
(SELECT PlaneID FROM tblBookings
WHERE (((([Time Start]<=[Forms]![frmMakeBooking]![cboStartTime]
AND [Time Finish]>=[Forms]![frmMakeBooking]![cboStartTime]))
OR (([Time Start]<=[Forms]![frmMakeBooking]![cboEndTime]
AND [Time Finish]>=[Forms]![frmMakeBooking]![cboEndTime])))
AND (tblBookings.Date=[Forms]![frmMakeBooking]![txtSelectedDate])))
AND (PlaneType=[Forms]![frmMakeBooking]![comboboxName]);
 

Make sure to change PlaneType to the name of the field in tblPlane and comboboxName to the name of the first combobox.

Mary

[pandaking]

That code you just gave me for the row source is perfect! Works like a dream :D
I don't know quite what to say, thank you would be a good start I guess.

I just need to work out how to refresh that second combo box (and the row source query), so that when I select a different time or a different plane type it updates itself :p

[MMcCarthy]

That code you just gave me for the row source is perfect! Works like a dream :D
I don't know quite what to say, thank you would be a good start I guess.

I just need to work out how to refresh that second combo box (and the row source query), so that when I select a different time or a different plane type it updates itself :p

You're welcome.

To requery the second combobox you will need to add an After Update event to the first combobox.

Private Sub FirstComboBox_AfterUpdate()
   Me!SecondCombobox.Requery
End Sub
 

Mary

[pandaking]

You star!
Thanks again for everything :)

[MMcCarthy]

You star!
Thanks again for everything :)

No problem ;)