Link to home
Start Free TrialLog in
Avatar of MorneqLippiatt
MorneqLippiatt

asked on

Removing used options from a dropdown list

Hi!

I am trying to build a scheduling database.  I currently have a client table, linked to an event table, linked to a dates table, linked to a locations table all in a one to many ralationship (i.e. one client, many events, one event many dates etc) I add resources to the events using a dropdown list.  I want a method in which I can automatically remove the resources already used on a date from the dropdown list so as to not use them again by accident - what is the best method for doing this and can it be adapted to my structure?

Cheers
Morne
Avatar of rockiroads
rockiroads
Flag of United States of America image

If your events combo bounded? i.e. it is based on a table or query?
if so then u have to filter your query so as to not pick the removed item
what is the Source of the Data for the DropDown list ?

I would bind the Dropdown list to a QUERY, such as (this is an example only, and you would need to change the query to match the fields and tables in your database)

Select Event from EventTable where EventID not in (Select EventID from ClienEventTable where ClientID = <Id of Current Client>)

You want to remove that one item, for just that one date? so if the date you added that resource is today....tomorrow, that resource is available again? So for instance, I get assigned mop detail today. so my name is off the list for today. Tomorrow though, I'm available for another assignment. Is that correct?
I'm thinking that if you create a small table to house the names as you assign them. Each time you assign a resource, it's name/id gets written there. So when the list box populates, it looks at this small table to see if a name is in there, and if it is, it's excluded from your list. this table can be automatically purged each morning or when the resources become available again. Let us know what you think.
J
Avatar of MorneqLippiatt
MorneqLippiatt

ASKER

The dropdown list is bound to a table containing the resource names. I have put a sample of my databse on http://www.contrasttraining.com/CTL-Web/Optimised/this_is_my_database.htm maybe this will help you see what I need to do.  As you will see, the information is enterd in a sequential way - providing the date of the event before the resource - by the time the user gets to the resource name (which is a dropdown) I want my database to evaluate (somehow) if a person (resource) has already been assigned to that date and hence remove it from the dropdown list (I have tried a querie but I'm stuck) I really would appreciate this guys! If you can tell me how to give more than 500 points I will!
Cheers
Morne
Just had a quick look and before I get to the question, I would like to suggest you change the name of tblDates to tblCourseDates, and the field DateID to CourseDateID.  It provides a much clearer picture of how your data  are joined.  Comments?
rather than binding the dropdown list to the underlying table, bind it to a query, which pulls the same fields, from the sdame table, but with an additional where clause which can then exclude those values that have already been selected for the person in question.

I do not see what you are refering to in the form that is in the ContrastTrainingSchedule database that you posted to the site.  There is only one form, frmClient, which shows the client ID, a dropdown list (bad idea) for the client name, and a sub-form showing Table,tblCourse.  I see nothing about scheduling.

I say that having a dropdownlist for client name is a bad idea because once you have specified the client id, the client name should be determined (the user should not need to select the client name from a dropdown list).  If anything, I would make the cleint ID a drop-down list so that the user could select from the user ids in the table, and once a Client id has been chosen, show the Client name that belongs to that ID.  This field (Client Name) is NOT (repeat NOT) bound to the underlying table.

perhaps you posted the wrong database?

AW
The posted database is just a shell - and only the table structure is what im using - the form(s) does nothing.  What you suggested with the dropdown being bound to the query is exactly what I had in mind but I have no idea how I would go about doing this...
make the RowSourceType of the combo box "Table/Query"

then, in the Click event handler of the First combo box:

Private Sub ClientCombo_Click()
   CourseCombo.RowSource = "Select CourseID, CourseNAME from tblCourse where ClientID= " & ClientCombo.Value
End Sub

then in the CourseCombo_Click event

Private Sub CourseCombo_Click()
    CourseDateCombo.RowSource = "Select DateID, CourseDate from tblDate where CourseID = " & CourseCombo.Value
End Sub

try that.  I am just qguessing at the names of the combo box controls.

AW
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
MorneqLippiatt:  Have you read my last post yet?
Thanks, but that's a bit of a surprise.  
Yeah sorry - clicked the wrong button - you owe AW one ;-)
MorneqLippiatt:  I asked to have the question re-opened.  
glad to be of assistance.

AW