Filter a Multi-value combo box field based on data entered in the

[Keenan]

Ok, I have tried to simplify and explain what I am trying to work towards for
a student timetable database.

For the Event table I want the "Weeks" field (which is a lookup multi value
combo box at the moment) to be filtered based on the "Day" field value. ie.
If Mon is the Day value, then the Weeks combo box only shows those records =
to Mon in the Weeks table. See below:

Table1: Event
Class - Day - Weeks - Teacher - Room
Science - Mon - 10,12,13 - John Smith - A10

Table2: Weeks (Combo box)
Day - Date - Week Number - Comment
Mon - 01-Mar-10 - 10 -
Mon - 08-Mar-10 - 11 - PubHol
Mon - 15-Mar-10 - 12 -
Mon - 22-Mar-10 - 13 -
(this list continues, I have week number records for each day of the week so
53 weeks x 7 days)

Thanks in advance.

 

[BruceM]

I'm not sure what you are trying to accomplish, but I have a few general
observations. If a date is know you can (and should) calculate the day,
week number, etc. as needed. For instance, to see the day of the week:
DayOfWeek: Format([DateField],"dddd")

For the week number:
WeekNumber: DatePart("ww",[DateField])

These expressions are shown as calculated query fields (i.e. put the
expression at the top of a blank column in query design view). You can set
the criteria for DayOfWeek to:
[Enter weekday name]

This is very general. I don't understand the purpose of the Weeks table (is
it the available weeks in the semester or something like that), and I don't
see how you can use a combo box to enter multiple weeks into a single field.
Access 2007 has multi-value fields as an option, but I doubt you can use a
combo box to enter multiple values directly. Perhaps you could enter the
weeks into a temp table, then concatenate the values into a multi-value
field, but even if possible, to what end?

 

[Keenan]

Hi Bruce

Thanks for the response. I'll try and clarify for you.

1. I understand and have used the Format and DatePart functions, etc, I was
just trying to simlify my question without going into to much detail.

2. We have a large timetable database system, and the database I'm working
on now will be used basically as a template for 50+ program coordinators to
enter their departments semester/term timetable for multiple courses, groups,
etc. Once complete the timetable will be sent to the timetables department
in csv format for uploading into the timetable system. The template is needed
to ensure accurancy of data and to avoid a large amount of data entry. To
give you an example, a basic course record would need to look like this:
Day - Start time - End Time - Weeks - Cat - Dept - Staff - Room
Mon - 9:00 - 18:00 - 1,2,3,6 - Profile Class - Science - John Smith - A10

3. I am using Access 2007 and use the multi-values field as a combo box
which works fine. I can select multiple week values from a drop down list
and those values are stored perfectly ie. 1,2,3,6. So I don't need to
concatenate as in previous versions of access.

4. The weeks table is needed because when timetabling, courses are booked on
the same day of the week over many weeks, never different days ie. The
Science course is booked 9am - 5pm each Monday on weeks 1, 2, 3 & 6. It
would need to be a new record if the course wanted Tuesday as well. There
needs to be 53 weeks x 7 days due to public holidays and etc, we don't want
bookings on these weeks. For example Week 11 we have a public holiday on
Monday so Week 11 can't be booked on a monday by courses, however Tuesday to
Sunday is still bookable in week 11. ie.
Day - Date - Week - Comment
Monday - 08-Mar-10 - 11 - PH
Tuesday - 09-Mar-10 - 11
Wednesday - 10-Mar-10 - 11
Thursday - 11-Mar-10 -11
Friday - 12-Mar-10 - 11
Saturday - 13-Mar-10 - 11
Sunday - 14-Mar-10 - 11
Monday - 15-Mar-10 - 12
Tuesday - 16-Mar-10 - 12
Wednesday - 17-Mar-10 - 12
Thursday - 18-Mar-10 - 12
Friday - 19-Mar-10 - 12
Saturday - 20-Mar-10 - 12
Sunday - 21-Mar-10 - 12

So If Monday is the day value then I need the combo box to only show the
Monday weeks.

Does that make sense?

I'm not sure what you are trying to accomplish, but I have a few general
observations. If a date is know you can (and should) calculate the day,
week number, etc. as needed. For instance, to see the day of the week:
DayOfWeek: Format([DateField],"dddd")

For the week number:
WeekNumber: DatePart("ww",[DateField])

These expressions are shown as calculated query fields (i.e. put the
expression at the top of a blank column in query design view). You can set
the criteria for DayOfWeek to:
[Enter weekday name]

This is very general. I don't understand the purpose of the Weeks table (is
it the available weeks in the semester or something like that), and I don't
see how you can use a combo box to enter multiple weeks into a single field.
Access 2007 has multi-value fields as an option, but I doubt you can use a
combo box to enter multiple values directly. Perhaps you could enter the
weeks into a temp table, then concatenate the values into a multi-value
field, but even if possible, to what end?

Ok, I have tried to simplify and explain what I am trying to work towards
for
a student timetable database.

For the Event table I want the "Weeks" field (which is a lookup multi
value
combo box at the moment) to be filtered based on the "Day" field value.
ie.
If Mon is the Day value, then the Weeks combo box only shows those records
=
to Mon in the Weeks table. See below:

Table1: Event
Class - Day - Weeks - Teacher - Room
Science - Mon - 10,12,13 - John Smith - A10

Table2: Weeks (Combo box)
Day - Date - Week Number - Comment
Mon - 01-Mar-10 - 10 -
Mon - 08-Mar-10 - 11 - PubHol
Mon - 15-Mar-10 - 12 -
Mon - 22-Mar-10 - 13 -
(this list continues, I have week number records for each day of the week
so
53 weeks x 7 days)

Thanks in advance.

 

[BruceM]

If a listing of dates is necessary, at least let Access calculate the
weekday and the week. That was my point about using the functions.

If applying a criteria as suggested limits the list to Monday dates, there
are ways other than a parameter prompt to do so.

Another option may be to use a calendar control. There is one option here:
http://allenbrowne.com/ser-51.html

The web page has links to other options. Although I have not looked at the
calendar control in detail, it should be possible to use a holidays table to
rule out certain dates.

Hi Bruce

Thanks for the response. I'll try and clarify for you.

1. I understand and have used the Format and DatePart functions, etc, I
was
just trying to simlify my question without going into to much detail.

2. We have a large timetable database system, and the database I'm working
on now will be used basically as a template for 50+ program coordinators
to
enter their departments semester/term timetable for multiple courses,
groups,
etc. Once complete the timetable will be sent to the timetables
department
in csv format for uploading into the timetable system. The template is
needed
to ensure accurancy of data and to avoid a large amount of data entry. To
give you an example, a basic course record would need to look like this:
Day - Start time - End Time - Weeks - Cat - Dept - Staff - Room
Mon - 9:00 - 18:00 - 1,2,3,6 - Profile Class - Science - John Smith - A10

3. I am using Access 2007 and use the multi-values field as a combo box
which works fine. I can select multiple week values from a drop down list
and those values are stored perfectly ie. 1,2,3,6. So I don't need to
concatenate as in previous versions of access.

4. The weeks table is needed because when timetabling, courses are booked
on
the same day of the week over many weeks, never different days ie. The
Science course is booked 9am - 5pm each Monday on weeks 1, 2, 3 & 6. It
would need to be a new record if the course wanted Tuesday as well. There
needs to be 53 weeks x 7 days due to public holidays and etc, we don't
want
bookings on these weeks. For example Week 11 we have a public holiday on
Monday so Week 11 can't be booked on a monday by courses, however Tuesday
to
Sunday is still bookable in week 11. ie.
Day - Date - Week - Comment
Monday - 08-Mar-10 - 11 - PH
Tuesday - 09-Mar-10 - 11
Wednesday - 10-Mar-10 - 11
Thursday - 11-Mar-10 -11
Friday - 12-Mar-10 - 11
Saturday - 13-Mar-10 - 11
Sunday - 14-Mar-10 - 11
Monday - 15-Mar-10 - 12
Tuesday - 16-Mar-10 - 12
Wednesday - 17-Mar-10 - 12
Thursday - 18-Mar-10 - 12
Friday - 19-Mar-10 - 12
Saturday - 20-Mar-10 - 12
Sunday - 21-Mar-10 - 12

So If Monday is the day value then I need the combo box to only show the
Monday weeks.

Does that make sense?

I'm not sure what you are trying to accomplish, but I have a few general
observations. If a date is know you can (and should) calculate the day,
week number, etc. as needed. For instance, to see the day of the week:
DayOfWeek: Format([DateField],"dddd")

For the week number:
WeekNumber: DatePart("ww",[DateField])

These expressions are shown as calculated query fields (i.e. put the
expression at the top of a blank column in query design view). You can
set
the criteria for DayOfWeek to:
[Enter weekday name]

This is very general. I don't understand the purpose of the Weeks table
(is
it the available weeks in the semester or something like that), and I
don't
see how you can use a combo box to enter multiple weeks into a single
field.
Access 2007 has multi-value fields as an option, but I doubt you can use
a
combo box to enter multiple values directly. Perhaps you could enter the
weeks into a temp table, then concatenate the values into a multi-value
field, but even if possible, to what end?

Ok, I have tried to simplify and explain what I am trying to work
towards
for
a student timetable database.

For the Event table I want the "Weeks" field (which is a lookup multi
value
combo box at the moment) to be filtered based on the "Day" field value.
ie.
If Mon is the Day value, then the Weeks combo box only shows those
records
=
to Mon in the Weeks table. See below:

Table1: Event
Class - Day - Weeks - Teacher - Room
Science - Mon - 10,12,13 - John Smith - A10

Table2: Weeks (Combo box)
Day - Date - Week Number - Comment
Mon - 01-Mar-10 - 10 -
Mon - 08-Mar-10 - 11 - PubHol
Mon - 15-Mar-10 - 12 -
Mon - 22-Mar-10 - 13 -
(this list continues, I have week number records for each day of the
week
so
53 weeks x 7 days)

Thanks in advance.

 

[Keenan]

Access does calculate the weekday and week, that is not the problem. If we
forget about dates for the moment. Can a multi-valued combo box be filtered
based on the value of a field in the current record? ie.

Combo box:
Item - Location - Code
TV - Living area - L1
Bed - Bedroom - B1
Computer - Study - S1
Table - Living area - L2

Record
Location - Item (multi valued combo box)
Living area - because Location = Living area, the combo box should only show
TV - L1 and Table - L2, and I can select both and they are stored as per the
code L2, L2.

Cheers

If a listing of dates is necessary, at least let Access calculate the
weekday and the week. That was my point about using the functions.

If applying a criteria as suggested limits the list to Monday dates, there
are ways other than a parameter prompt to do so.

Another option may be to use a calendar control. There is one option here:
http://allenbrowne.com/ser-51.html

The web page has links to other options. Although I have not looked at the
calendar control in detail, it should be possible to use a holidays table to
rule out certain dates.

Hi Bruce

Thanks for the response. I'll try and clarify for you.

1. I understand and have used the Format and DatePart functions, etc, I
was
just trying to simlify my question without going into to much detail.

2. We have a large timetable database system, and the database I'm working
on now will be used basically as a template for 50+ program coordinators
to
enter their departments semester/term timetable for multiple courses,
groups,
etc. Once complete the timetable will be sent to the timetables
department
in csv format for uploading into the timetable system. The template is
needed
to ensure accurancy of data and to avoid a large amount of data entry. To
give you an example, a basic course record would need to look like this:
Day - Start time - End Time - Weeks - Cat - Dept - Staff - Room
Mon - 9:00 - 18:00 - 1,2,3,6 - Profile Class - Science - John Smith - A10

3. I am using Access 2007 and use the multi-values field as a combo box
which works fine. I can select multiple week values from a drop down list
and those values are stored perfectly ie. 1,2,3,6. So I don't need to
concatenate as in previous versions of access.

4. The weeks table is needed because when timetabling, courses are booked
on
the same day of the week over many weeks, never different days ie. The
Science course is booked 9am - 5pm each Monday on weeks 1, 2, 3 & 6. It
would need to be a new record if the course wanted Tuesday as well. There
needs to be 53 weeks x 7 days due to public holidays and etc, we don't
want
bookings on these weeks. For example Week 11 we have a public holiday on
Monday so Week 11 can't be booked on a monday by courses, however Tuesday
to
Sunday is still bookable in week 11. ie.
Day - Date - Week - Comment
Monday - 08-Mar-10 - 11 - PH
Tuesday - 09-Mar-10 - 11
Wednesday - 10-Mar-10 - 11
Thursday - 11-Mar-10 -11
Friday - 12-Mar-10 - 11
Saturday - 13-Mar-10 - 11
Sunday - 14-Mar-10 - 11
Monday - 15-Mar-10 - 12
Tuesday - 16-Mar-10 - 12
Wednesday - 17-Mar-10 - 12
Thursday - 18-Mar-10 - 12
Friday - 19-Mar-10 - 12
Saturday - 20-Mar-10 - 12
Sunday - 21-Mar-10 - 12

So If Monday is the day value then I need the combo box to only show the
Monday weeks.

Does that make sense?

I'm not sure what you are trying to accomplish, but I have a few general
observations. If a date is know you can (and should) calculate the day,
week number, etc. as needed. For instance, to see the day of the week:
DayOfWeek: Format([DateField],"dddd")

For the week number:
WeekNumber: DatePart("ww",[DateField])

These expressions are shown as calculated query fields (i.e. put the
expression at the top of a blank column in query design view). You can
set
the criteria for DayOfWeek to:
[Enter weekday name]

This is very general. I don't understand the purpose of the Weeks table
(is
it the available weeks in the semester or something like that), and I
don't
see how you can use a combo box to enter multiple weeks into a single
field.
Access 2007 has multi-value fields as an option, but I doubt you can use
a
combo box to enter multiple values directly. Perhaps you could enter the
weeks into a temp table, then concatenate the values into a multi-value
field, but even if possible, to what end?

Ok, I have tried to simplify and explain what I am trying to work
towards
for
a student timetable database.

For the Event table I want the "Weeks" field (which is a lookup multi
value
combo box at the moment) to be filtered based on the "Day" field value.
ie.
If Mon is the Day value, then the Weeks combo box only shows those
records
=
to Mon in the Weeks table. See below:

Table1: Event
Class - Day - Weeks - Teacher - Room
Science - Mon - 10,12,13 - John Smith - A10

Table2: Weeks (Combo box)
Day - Date - Week Number - Comment
Mon - 01-Mar-10 - 10 -
Mon - 08-Mar-10 - 11 - PubHol
Mon - 15-Mar-10 - 12 -
Mon - 22-Mar-10 - 13 -
(this list continues, I have week number records for each day of the
week
so
53 weeks x 7 days)

Thanks in advance.

 

[BruceM]

If this is a question specifically about using a multi-value field you
should probably repost the question as such. I am not familiar enough with
Access 2007 to be able to answer the question. Also, I find I am unable to
follow the question. My understanidng was that you want to store a list of
integers (week numbers) in the multi-value field, but now it seems it is
something other than that.

If the question is whether you can set the row source of the multi-value
combo box (cboShowWeeks) based on the selection in the first combo box, the
answer is yes. I suggested using a query with a parameter (a text box on
the form) to set the date:

SELECT [ClassDate],
DatePart("ww",[ClassDate]) AS WeekNumber
FROM tblDate
WHERE Format([ClassDate],"dddd") =
Forms!frmMain!cboClassDay
ORDER BY [ClassDate]

frmMain is the name of the form, and cboClassDay is the combo box from which
the weekday is selected. In the After Update event of cboClassDay:

Me.cboShowWeeks.Requery

Access does calculate the weekday and week, that is not the problem. If
we
forget about dates for the moment. Can a multi-valued combo box be
filtered
based on the value of a field in the current record? ie.

Combo box:
Item - Location - Code
TV - Living area - L1
Bed - Bedroom - B1
Computer - Study - S1
Table - Living area - L2

Record
Location - Item (multi valued combo box)
Living area - because Location = Living area, the combo box should only
show
TV - L1 and Table - L2, and I can select both and they are stored as per
the
code L2, L2.

Cheers

If a listing of dates is necessary, at least let Access calculate the
weekday and the week. That was my point about using the functions.

If applying a criteria as suggested limits the list to Monday dates,
there
are ways other than a parameter prompt to do so.

Another option may be to use a calendar control. There is one option
here:
http://allenbrowne.com/ser-51.html

The web page has links to other options. Although I have not looked at
the
calendar control in detail, it should be possible to use a holidays table
to
rule out certain dates.

Hi Bruce

Thanks for the response. I'll try and clarify for you.

1. I understand and have used the Format and DatePart functions, etc, I
was
just trying to simlify my question without going into to much detail.

2. We have a large timetable database system, and the database I'm
working
on now will be used basically as a template for 50+ program
coordinators
to
enter their departments semester/term timetable for multiple courses,
groups,
etc. Once complete the timetable will be sent to the timetables
department
in csv format for uploading into the timetable system. The template is
needed
to ensure accurancy of data and to avoid a large amount of data entry.
To
give you an example, a basic course record would need to look like
this:
Day - Start time - End Time - Weeks - Cat - Dept - Staff - Room
Mon - 9:00 - 18:00 - 1,2,3,6 - Profile Class - Science - John Smith -
A10

3. I am using Access 2007 and use the multi-values field as a combo box
which works fine. I can select multiple week values from a drop down
list
and those values are stored perfectly ie. 1,2,3,6. So I don't need to
concatenate as in previous versions of access.

4. The weeks table is needed because when timetabling, courses are
booked
on
the same day of the week over many weeks, never different days ie. The
Science course is booked 9am - 5pm each Monday on weeks 1, 2, 3 & 6.
It
would need to be a new record if the course wanted Tuesday as well.
There
needs to be 53 weeks x 7 days due to public holidays and etc, we don't
want
bookings on these weeks. For example Week 11 we have a public holiday
on
Monday so Week 11 can't be booked on a monday by courses, however
Tuesday
to
Sunday is still bookable in week 11. ie.
Day - Date - Week - Comment
Monday - 08-Mar-10 - 11 - PH
Tuesday - 09-Mar-10 - 11
Wednesday - 10-Mar-10 - 11
Thursday - 11-Mar-10 -11
Friday - 12-Mar-10 - 11
Saturday - 13-Mar-10 - 11
Sunday - 14-Mar-10 - 11
Monday - 15-Mar-10 - 12
Tuesday - 16-Mar-10 - 12
Wednesday - 17-Mar-10 - 12
Thursday - 18-Mar-10 - 12
Friday - 19-Mar-10 - 12
Saturday - 20-Mar-10 - 12
Sunday - 21-Mar-10 - 12

So If Monday is the day value then I need the combo box to only show
the
Monday weeks.

Does that make sense?

:

I'm not sure what you are trying to accomplish, but I have a few
general
observations. If a date is know you can (and should) calculate the
day,
week number, etc. as needed. For instance, to see the day of the
week:
DayOfWeek: Format([DateField],"dddd")

For the week number:
WeekNumber: DatePart("ww",[DateField])

These expressions are shown as calculated query fields (i.e. put the
expression at the top of a blank column in query design view). You
can
set
the criteria for DayOfWeek to:
[Enter weekday name]

This is very general. I don't understand the purpose of the Weeks
table
(is
it the available weeks in the semester or something like that), and I
don't
see how you can use a combo box to enter multiple weeks into a single
field.
Access 2007 has multi-value fields as an option, but I doubt you can
use
a
combo box to enter multiple values directly. Perhaps you could enter
the
weeks into a temp table, then concatenate the values into a
multi-value
field, but even if possible, to what end?

Ok, I have tried to simplify and explain what I am trying to work
towards
for
a student timetable database.

For the Event table I want the "Weeks" field (which is a lookup
multi
value
combo box at the moment) to be filtered based on the "Day" field
value.
ie.
If Mon is the Day value, then the Weeks combo box only shows those
records
=
to Mon in the Weeks table. See below:

Table1: Event
Class - Day - Weeks - Teacher - Room
Science - Mon - 10,12,13 - John Smith - A10

Table2: Weeks (Combo box)
Day - Date - Week Number - Comment
Mon - 01-Mar-10 - 10 -
Mon - 08-Mar-10 - 11 - PubHol
Mon - 15-Mar-10 - 12 -
Mon - 22-Mar-10 - 13 -
(this list continues, I have week number records for each day of the
week
so
53 weeks x 7 days)

Thanks in advance.

 

[Keenan]

Thanks Bruce. Sorry for the confusion. the query with a parameter will
assist me greatly.

If this is a question specifically about using a multi-value field you
should probably repost the question as such. I am not familiar enough with
Access 2007 to be able to answer the question. Also, I find I am unable to
follow the question. My understanidng was that you want to store a list of
integers (week numbers) in the multi-value field, but now it seems it is
something other than that.

If the question is whether you can set the row source of the multi-value
combo box (cboShowWeeks) based on the selection in the first combo box, the
answer is yes. I suggested using a query with a parameter (a text box on
the form) to set the date:

SELECT [ClassDate],
DatePart("ww",[ClassDate]) AS WeekNumber
FROM tblDate
WHERE Format([ClassDate],"dddd") =
Forms!frmMain!cboClassDay
ORDER BY [ClassDate]

frmMain is the name of the form, and cboClassDay is the combo box from which
the weekday is selected. In the After Update event of cboClassDay:

Me.cboShowWeeks.Requery

Access does calculate the weekday and week, that is not the problem. If
we
forget about dates for the moment. Can a multi-valued combo box be
filtered
based on the value of a field in the current record? ie.

Combo box:
Item - Location - Code
TV - Living area - L1
Bed - Bedroom - B1
Computer - Study - S1
Table - Living area - L2

Record
Location - Item (multi valued combo box)
Living area - because Location = Living area, the combo box should only
show
TV - L1 and Table - L2, and I can select both and they are stored as per
the
code L2, L2.

Cheers

If a listing of dates is necessary, at least let Access calculate the
weekday and the week. That was my point about using the functions.

If applying a criteria as suggested limits the list to Monday dates,
there
are ways other than a parameter prompt to do so.

Another option may be to use a calendar control. There is one option
here:
http://allenbrowne.com/ser-51.html

The web page has links to other options. Although I have not looked at
the
calendar control in detail, it should be possible to use a holidays table
to
rule out certain dates.


Hi Bruce

Thanks for the response. I'll try and clarify for you.

1. I understand and have used the Format and DatePart functions, etc, I
was
just trying to simlify my question without going into to much detail.

2. We have a large timetable database system, and the database I'm
working
on now will be used basically as a template for 50+ program
coordinators
to
enter their departments semester/term timetable for multiple courses,
groups,
etc. Once complete the timetable will be sent to the timetables
department
in csv format for uploading into the timetable system. The template is
needed
to ensure accurancy of data and to avoid a large amount of data entry.
To
give you an example, a basic course record would need to look like
this:
Day - Start time - End Time - Weeks - Cat - Dept - Staff - Room
Mon - 9:00 - 18:00 - 1,2,3,6 - Profile Class - Science - John Smith -
A10

3. I am using Access 2007 and use the multi-values field as a combo box
which works fine. I can select multiple week values from a drop down
list
and those values are stored perfectly ie. 1,2,3,6. So I don't need to
concatenate as in previous versions of access.

4. The weeks table is needed because when timetabling, courses are
booked
on
the same day of the week over many weeks, never different days ie. The
Science course is booked 9am - 5pm each Monday on weeks 1, 2, 3 & 6.
It
would need to be a new record if the course wanted Tuesday as well.
There
needs to be 53 weeks x 7 days due to public holidays and etc, we don't
want
bookings on these weeks. For example Week 11 we have a public holiday
on
Monday so Week 11 can't be booked on a monday by courses, however
Tuesday
to
Sunday is still bookable in week 11. ie.
Day - Date - Week - Comment
Monday - 08-Mar-10 - 11 - PH
Tuesday - 09-Mar-10 - 11
Wednesday - 10-Mar-10 - 11
Thursday - 11-Mar-10 -11
Friday - 12-Mar-10 - 11
Saturday - 13-Mar-10 - 11
Sunday - 14-Mar-10 - 11
Monday - 15-Mar-10 - 12
Tuesday - 16-Mar-10 - 12
Wednesday - 17-Mar-10 - 12
Thursday - 18-Mar-10 - 12
Friday - 19-Mar-10 - 12
Saturday - 20-Mar-10 - 12
Sunday - 21-Mar-10 - 12

So If Monday is the day value then I need the combo box to only show
the
Monday weeks.

Does that make sense?

:

I'm not sure what you are trying to accomplish, but I have a few
general
observations. If a date is know you can (and should) calculate the
day,
week number, etc. as needed. For instance, to see the day of the
week:
DayOfWeek: Format([DateField],"dddd")

For the week number:
WeekNumber: DatePart("ww",[DateField])

These expressions are shown as calculated query fields (i.e. put the
expression at the top of a blank column in query design view). You
can
set
the criteria for DayOfWeek to:
[Enter weekday name]

This is very general. I don't understand the purpose of the Weeks
table
(is
it the available weeks in the semester or something like that), and I
don't
see how you can use a combo box to enter multiple weeks into a single
field.
Access 2007 has multi-value fields as an option, but I doubt you can
use
a
combo box to enter multiple values directly. Perhaps you could enter
the
weeks into a temp table, then concatenate the values into a
multi-value
field, but even if possible, to what end?

Ok, I have tried to simplify and explain what I am trying to work
towards
for
a student timetable database.

For the Event table I want the "Weeks" field (which is a lookup
multi
value
combo box at the moment) to be filtered based on the "Day" field
value.
ie.
If Mon is the Day value, then the Weeks combo box only shows those
records
=
to Mon in the Weeks table. See below:

Table1: Event
Class - Day - Weeks - Teacher - Room
Science - Mon - 10,12,13 - John Smith - A10

Table2: Weeks (Combo box)
Day - Date - Week Number - Comment
Mon - 01-Mar-10 - 10 -
Mon - 08-Mar-10 - 11 - PubHol
Mon - 15-Mar-10 - 12 -
Mon - 22-Mar-10 - 13 -
(this list continues, I have week number records for each day of the
week
so
53 weeks x 7 days)

Thanks in advance.