Is it possible to prevent a user from scheduling a date on a drop down menu?

[wedd]

On my current databse I am trying to develop a schedule system (drop down menu with start and finish times with dates) were the user is unable to double book a customer to hire a venue for an event. Is it possible based on times of a venue being booked at a specific time that if the user booked a time a pop-up message would appear and explain to them that the venue is currently booked at that location and will be available at another particular tims? Is this possible to perform with code? If so how can it be done? Do you have any good examples?



Thanks

[Imdabaum]

On my current databse I am trying to develop a schedule system (drop down menu with start and finish times with dates) were the user is unable to double book a customer to hire a venue for an event. Is it possible based on times of a venue being booked at a specific time that if the user booked a time a pop-up message would appear and explain to them that the venue is currently booked at that location and will be available at another particular tims? Is this possible to perform with code? If so how can it be done? Do you have any good examples?



Thanks

Yes.
Say you hired venue A from 9pm-12am on 31/10/2010 and someone else looked at venue A and attempted to hire them from 10pm-1am

There would be a record for venue A with start date 9pm. Right? 9pm < 10pm and 12am is after 10pm, so the venue is booked and cannot start at 10pm.

So something like this
[vba]
'Lookup records for venue A on Date
Dim rst as DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT startTime FROM hireling WHERE venue ='A'" _
"and startDate=" & Me.Date", dbOpenDynaset)
'Then you can loop through the recordset and compare Me.Date and Me.Time
'to the rst.Fields("startTime") ideally there should only be one record
If (rst.Fields("startTime")<Me.StartTime) And rst.Fields("EndTime")>Me.StartTime Then
' The venue was hired before the requested start time and it won't be over before the requested start time.
'.... add in additional logic to verify if there's a possibility to schedule an
'appointment after the party. Keep in mind that just because the party ends
'at 1am doesn't mean it will be ready at 1am. May require a duration for
'cleanup
End if
[/vba]

To provide a coded solution, I would need to know how your table was set up. I would assume there is at least a begintime and an endtime, a date of hire. There are also other alternatives depending on how you're structured. What the rules are for required break duration between events.

-I hate it when I pseudo code in code.

[wedd]

This is excellent! Basically in a nutshell there are 3 different organisations that hire out their facilities to the general public either for board meetings meetings, conferences sessions, politcal party speeches, weddings, gym facilities etc...and the times range from 9am to 22:00pm. So, this process will make it easier for the user to make an appointment. Can I adapt the code for instance if something has been cancelled? Do you have code that can adapt to this change?

Thanks for your help

[Imdabaum]

I've never done anything like it so I don't have any sample code except what comes out of my head. But yes you could adapt it. ALL code I share PROBABLY SHOULD be adapted and changed so that it works perfectly.

As far as canceling goes, you can either delete the record that has the schedule... or ideally add a Canceled field as Yes/No to the table. Then you can modify the above select statement to include all records where Canceled =0. If canceled = yes and the date and time is the same, then who cares right? They canceled let's put someone else in the slot.

If you need to see how many cancelations are occurring this gives you the capability. If you delete data, you have less data to manage, but no data to analyze if you want to improve your processes or see how often a customer is canceling.