How to filter/remove combo box values if previously used/entered

[nezzy]

Hi,

I'm new to the forum so please excuse my lack of knowledge.

I have a combo box containing a training date (combo box is sourced from tblTrainingCourse). I'd like to filter this combo box if a particular training date is entered into a form a pre-defined number of times (A training course only has a limited number of places so once a particular number of individuals have been assigned to this course, it should be removed from the combo box).

Structure:
tblIndividual
IND ID
Name
etc...

tblTraining Course
TC ID
Course name
no. of course places

tbl Attendance
ATT ID
IND ID
TC ID

Relationships:
tblIndividual 1.....* tblAttendance *.....1 Training Course

Hope this makes sense!

[ADezii]

1. To what Table/Query is your Form Bound?
2. What Table does the Training Date Field reside in, I don't see it in your structure?

[nezzy]

1. To what Table/Query is your Form Bound?
2. What Table does the Training Date Field reside in, I don't see it in your structure?

Sorry. Form is bound to tblAttendance. Training Date field resides in tbl Course Dates.

Structure:
tblIndividual
IND ID
Name
etc...

tblTraining Course
TC ID
Course name
no. of course places

tblCourse Dates
CD ID
TC ID
Date

tbl Attendance
ATT ID
IND ID
CD ID

Relationships:
tblIndividual 1....* tblAttendance *....1tblCourseDates*...1 tblTrainingCourse

[ADezii]

1. Assuming the following Relationship:
   

   tblTrainingCourse.[TC ID](1) ==>  tblCourseDates.[TC ID](MANY)

2. Assuming a [Training Date] Field exists in tblTrainingCourse
3. The Row Source of your Combo Box is derived from the Dates in the [Training Date] Field in tblTrainingCourse
4. You wish to display only Dates in your Combo Box which occur less than 6 times in tblCourseDates
5. This SQL Statement should do the trick.
   

   SELECT tblTrainingCourse.[Training Date]
   FROM tblTrainingCourse INNER JOIN tblCourseDates ON tblTrainingCourse.[TC ID] = tblCourseDates.[TC ID]
   GROUP BY tblTrainingCourse.[Training Date]
   HAVING Count(tblCourseDates.[Training Date])<6
   ORDER BY tblTrainingCourse.[Training Date];

6. P.S. - You must Requery the Combo Box after each Record addition in the Form.
7. Any questions, feel free to ask.

[nezzy]

The Training Date actually resides in tblCourse Date but I understand the principle.

I've tried something similar already but have found that once an entry is removed from the combo box, any previous entries of that date dissapear from the form aswell (any entries go blank).

[ADezii]

The Training Date actually resides in tblCourse Date but I understand the principle.

I've tried something similar already but have found that once an entry is removed from the combo box, any previous entries of that date dissapear from the form aswell (any entries go blank).

I'm assuming the Combo Box is 'Unbound' and used only for selecting an available Training Date, is this correct? Given the above statment, removing a Date from the Row Source of the Combo should have no effect whatsoever on existing values.

[nezzy]

I'm assuming the Combo Box is 'Unbound' and used only for selecting an available Training Date, is this correct? Given the above statment, removing a Date from the Row Source of the Combo should have no effect whatsoever on existing values.

Thanks. How do I un-bind the combo box?

[nezzy]

....................

[nezzy]

Found a workaround. Thanks for your help!