Choosing date parameter for query, to populate form

[JAKBLUE]

Introduction
I am creating an ‘appointments’ database in Access, for a company that hires vehicles and drivers to the public. Each individual appointment is created in a form, where date, time and destination etc are recorded. The user can also choose which vehicle and driver to assign to the appointment (drivers and vehicles lists are held in separate tables) – though this may not be known at the time the appointment is created.

Having gathered these appointments, the company then need to review all the appointments for a specific day. Therefore I have created another form, which is like a diary, showing all the appointments for one day. This is sourced from a query I have made, which is like:

SELECT (various data) FROM appointment_table, customer_table, vehicle_table, driver_table WHERE appointment_table.date = [&date] and ...

In this form they can then get an overview of how their vehicles and drivers are assigned for that day, and then change them to get the ‘best fit’ for all appointments on that day.

The Objective
What I want to do is improve the date selection for this form.
Currently the user has to type in a date (for the query) and then the form opens for that date. When they want to select another date, they close the form and then open it again and type another date.

I want the form to open without a date being selected. Instead a calendar control will appear in the header area of the form, allowing the user to click a date. The query is then run for this date and the form is populated.
When the user wants to review another day then s/he clicks a different date on the calendar control and the query is re-run and the form re-populated for the new date.

Can anyone tell me how to go about this?

 

[Alruk]

Ive never used the calander control before but had a quick look.

I noticed on the poperties tab on the form designer it doesnt have a click event.

You can create a click event handler in the visual basic window for your calander control.

In the click event reset the record source for the active form.

Something like below should point you towards finding a solution.

Private Sub CalanderControl_Click()

Dim FilterDate As Date

FilterDate = Me.CalanderControl.Value

Me.RecordSource = "SELECT (various data) FROM appointment_table, customer_table, vehicle_table, driver_table WHERE appointment_table.date = " & FilterDate & " and ... "

Me.Requery

End Sub