Next problem - access to recognise appointment clashes

[keri]

Hello again,

While I try and solve the calender issue I also have another problem.

I have a form & table (appointments) where the user records
appointments in the future. The fields include date, time and duration.
I would like a pop up box to appear when registering an appointment if
the time / days clash with another appointment.

Somehow I need to create a table or query that blocks out sections of
time that are "booked" so I can create the pop up box based on a If or
query.

In a similar vein I would like a pop up box to warn if a user was
booking 2 appointments in different areas in the same day. (I already
have a filter category for the area so it's more about how to get
access to recognise that there is already an appointment booked on that
day for a different area).

Has anybody done something like this before?

[jlepack]

tbl_user
user_id - PK (Autonumber)
user_name (Text)
.... anything else you want about the user...

tbl_area
area_id - PK (Autonumber)
area_name (Text)
.... anything else you want about the area...

tbl_appointment
appt_id - PK (Autonumber)
area_id - FK (Number)
appt_start (Date/Time)
appt_end (Date/Time)

tbl_schedule
sched_id - PK (Autonumber)
appt_id - FK (Number)
user_id - FK (Number)

Create these tables. Click on Tools->Relationships. Add each of these
tables and join the fields with the same names to each other and click
"Enforce Referential Integrity"

Now what you are looking at is a very normalized relational database.
A "user" "schedules" an "appointment" which takes place in an "area".

Now create a form to access tbl_user, tbl_area, and tbl_appointment
just using the wizard.

Now, notice that the field for area_id on the appointment form just
takes numbers. Right click on it and click "Change To" and select
"Combo Box". Right click on your new combo box and click properties.
Go to data. Make sure "Rowsource Type" is "Table/Query". Now click
"Row Source", click the "..." beside it. You want to include area_id
and area_name from tbl_area. Change the "Column Count" to 2. Now use
your form. If when you choose an item from the combo box it says "the
value you entered isn't valid for this field" then you need to change
the "Bound Column" to the other number, if it's 1 make it 2 or visa
versa.

Next attempt the same thing for the schedule table, note that you will
need two combo boxes.

After you get that down I'll help with the next part.

Cheers,
Jason Lepack

keri wrote:

Hello again,

While I try and solve the calender issue I also have another problem.

I have a form & table (appointments) where the user records
appointments in the future. The fields include date, time and duration.
I would like a pop up box to appear when registering an appointment if
the time / days clash with another appointment.

Somehow I need to create a table or query that blocks out sections of
time that are "booked" so I can create the pop up box based on a If or
query.

In a similar vein I would like a pop up box to warn if a user was
booking 2 appointments in different areas in the same day. (I already
have a filter category for the area so it's more about how to get
access to recognise that there is already an appointment booked on that
day for a different area).

Has anybody done something like this before?

[keri]

I could kiss you!!! Starting now.Thanks so much

jlepack wrote:

tbl_user
user_id - PK (Autonumber)
user_name (Text)
... anything else you want about the user...

tbl_area
area_id - PK (Autonumber)
area_name (Text)
... anything else you want about the area...

tbl_appointment
appt_id - PK (Autonumber)
area_id - FK (Number)
appt_start (Date/Time)
appt_end (Date/Time)

tbl_schedule
sched_id - PK (Autonumber)
appt_id - FK (Number)
user_id - FK (Number)

Create these tables. Click on Tools->Relationships. Add each of these
tables and join the fields with the same names to each other and click
"Enforce Referential Integrity"

Now what you are looking at is a very normalized relational database.
A "user" "schedules" an "appointment" which takes place in an "area".

Now create a form to access tbl_user, tbl_area, and tbl_appointment
just using the wizard.

Now, notice that the field for area_id on the appointment form just
takes numbers. Right click on it and click "Change To" and select
"Combo Box". Right click on your new combo box and click properties.
Go to data. Make sure "Rowsource Type" is "Table/Query". Now click
"Row Source", click the "..." beside it. You want to include area_id
and area_name from tbl_area. Change the "Column Count" to 2. Now use
your form. If when you choose an item from the combo box it says "the
value you entered isn't valid for this field" then you need to change
the "Bound Column" to the other number, if it's 1 make it 2 or visa
versa.

Next attempt the same thing for the schedule table, note that you will
need two combo boxes.

After you get that down I'll help with the next part.

Cheers,
Jason Lepack

keri wrote:

Hello again,

While I try and solve the calender issue I also have another problem.

I have a form & table (appointments) where the user records
appointments in the future. The fields include date, time and duration.
I would like a pop up box to appear when registering an appointment if
the time / days clash with another appointment.

Somehow I need to create a table or query that blocks out sections of
time that are "booked" so I can create the pop up box based on a If or
query.

In a similar vein I would like a pop up box to warn if a user was
booking 2 appointments in different areas in the same day. (I already
have a filter category for the area so it's more about how to get
access to recognise that there is already an appointment booked on that
day for a different area).

Has anybody done something like this before?

[jlepack]

Ah sorry, in the tbl_appointment.
format appt_start and appt_end as "Short Time" and add
appt_date (Date/Time) and format it as "Short Date"

jlepack wrote:

tbl_user
user_id - PK (Autonumber)
user_name (Text)
... anything else you want about the user...

tbl_area
area_id - PK (Autonumber)
area_name (Text)
... anything else you want about the area...

tbl_appointment
appt_id - PK (Autonumber)
area_id - FK (Number)
appt_start (Date/Time)
appt_end (Date/Time)

tbl_schedule
sched_id - PK (Autonumber)
appt_id - FK (Number)
user_id - FK (Number)

Create these tables. Click on Tools->Relationships. Add each of these
tables and join the fields with the same names to each other and click
"Enforce Referential Integrity"

Now what you are looking at is a very normalized relational database.
A "user" "schedules" an "appointment" which takes place in an "area".

Now create a form to access tbl_user, tbl_area, and tbl_appointment
just using the wizard.

Now, notice that the field for area_id on the appointment form just
takes numbers. Right click on it and click "Change To" and select
"Combo Box". Right click on your new combo box and click properties.
Go to data. Make sure "Rowsource Type" is "Table/Query". Now click
"Row Source", click the "..." beside it. You want to include area_id
and area_name from tbl_area. Change the "Column Count" to 2. Now use
your form. If when you choose an item from the combo box it says "the
value you entered isn't valid for this field" then you need to change
the "Bound Column" to the other number, if it's 1 make it 2 or visa
versa.

Next attempt the same thing for the schedule table, note that you will
need two combo boxes.

After you get that down I'll help with the next part.

Cheers,
Jason Lepack

keri wrote:

Hello again,

While I try and solve the calender issue I also have another problem.

I have a form & table (appointments) where the user records
appointments in the future. The fields include date, time and duration.
I would like a pop up box to appear when registering an appointment if
the time / days clash with another appointment.

Somehow I need to create a table or query that blocks out sections of
time that are "booked" so I can create the pop up box based on a If or
query.

In a similar vein I would like a pop up box to warn if a user was
booking 2 appointments in different areas in the same day. (I already
have a filter category for the area so it's more about how to get
access to recognise that there is already an appointment booked on that
day for a different area).

Has anybody done something like this before?

[jlepack]

And for the combo box set "Limit To List" to "Yes"
jlepack wrote:

Ah sorry, in the tbl_appointment.
format appt_start and appt_end as "Short Time" and add
appt_date (Date/Time) and format it as "Short Date"

jlepack wrote:

tbl_user
user_id - PK (Autonumber)
user_name (Text)
... anything else you want about the user...

tbl_area
area_id - PK (Autonumber)
area_name (Text)
... anything else you want about the area...

tbl_appointment
appt_id - PK (Autonumber)
area_id - FK (Number)
appt_start (Date/Time)
appt_end (Date/Time)

tbl_schedule
sched_id - PK (Autonumber)
appt_id - FK (Number)
user_id - FK (Number)

Create these tables. Click on Tools->Relationships. Add each of these
tables and join the fields with the same names to each other and click
"Enforce Referential Integrity"

Now what you are looking at is a very normalized relational database.
A "user" "schedules" an "appointment" which takes place in an "area".

Now create a form to access tbl_user, tbl_area, and tbl_appointment
just using the wizard.

Now, notice that the field for area_id on the appointment form just
takes numbers. Right click on it and click "Change To" and select
"Combo Box". Right click on your new combo box and click properties.
Go to data. Make sure "Rowsource Type" is "Table/Query". Now click
"Row Source", click the "..." beside it. You want to include area_id
and area_name from tbl_area. Change the "Column Count" to 2. Now use
your form. If when you choose an item from the combo box it says "the
value you entered isn't valid for this field" then you need to change
the "Bound Column" to the other number, if it's 1 make it 2 or visa
versa.

Next attempt the same thing for the schedule table, note that you will
need two combo boxes.

After you get that down I'll help with the next part.

Cheers,
Jason Lepack

keri wrote:

Hello again,
>
While I try and solve the calender issue I also have another problem.
>
I have a form & table (appointments) where the user records
appointments in the future. The fields include date, time and duration.
I would like a pop up box to appear when registering an appointment if
the time / days clash with another appointment.
>
Somehow I need to create a table or query that blocks out sections of
time that are "booked" so I can create the pop up box based on a If or
query.
>
In a similar vein I would like a pop up box to warn if a user was
booking 2 appointments in different areas in the same day. (I already
have a filter category for the area so it's more about how to get
access to recognise that there is already an appointment booked on that
day for a different area).
>
Has anybody done something like this before?