By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

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

P: 6
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!
Oct 11 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,679
  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?
Oct 11 '08 #2

P: 6
  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
Oct 12 '08 #3

ADezii
Expert 5K+
P: 8,679
  1. Assuming the following Relationship:
    Expand|Select|Wrap|Line Numbers
    1. 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.
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblTrainingCourse.[Training Date]
    2. FROM tblTrainingCourse INNER JOIN tblCourseDates ON tblTrainingCourse.[TC ID] = tblCourseDates.[TC ID]
    3. GROUP BY tblTrainingCourse.[Training Date]
    4. HAVING Count(tblCourseDates.[Training Date])<6
    5. 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.
Oct 12 '08 #4

P: 6
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).
Oct 12 '08 #5

ADezii
Expert 5K+
P: 8,679
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.
Oct 12 '08 #6

P: 6
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?
Oct 12 '08 #7

P: 6
....................
Oct 12 '08 #8

P: 6
Found a workaround. Thanks for your help!
Oct 12 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.