Hi, I don't know if this can be do 1

[1ooo1]

Hi,
I don't know if this can be done, but here goes....
I am trying to make a roster of sorts, whereby I need to insert a name into a work position for a day.
1 row would represent a day, with columns for each position...eg Bay1 Bay2 Bay3 etc etc
I want to have a ComboBox to choose a name for each position, but.....as I go along, I want the list in the ComboBox to diminish, so only unused names for that day are displayed as I go through each position. Does that make sense...I want to do this to prevent duplicating a person in 2 positions on a given day, and also to see who is left on the list for the remaining positions !! There will be about 20 names and 15 positions.
Has anybody got any ideas?

Ken

 

[SBendBuckeye]

Hello Ken,

Here is one way that you could do this. Let's say you have a field called UserID associated with your folks.

1. Create a temporary table named NowAssigned or whatever

2. In the After Update code for your assignment comboboxes insert the just selected UserID into table NowAssigned

3. Add the following to the Where clause for your combobox rowsource - Where UserID Not In (Select UserID From NowAssigned)

Good Luck!

 

[Trendsetter]

Yes this can be done!
What you need to do is set up the query that populates the combo with 2 tables that are joined by say the EmployeeID in each table. Then build the query so that if an EmployeeID appears in the Roster table for that Day then it cannot appear in the dataset produced by the query.
The SQL statement IN or NOT IN would be used to detect the difference.
It is difficult to be specific without knowing the table,filed and control names.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[1ooo1]

Thanks for the advice....
I may be getting out of my depth here, seems a bit complicated....I'll see if I can implement your ideas.

Ken

 

[Trendsetter]

Hi KenI have a sample of this in listbox form instead of combos but the principle is just the same.
Let me know if you want it and where to send it!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[1ooo1]

Hi Frank,
I would appreciate it if you could send me a copy of your ListBox, here's my address
ungerk@warringah.nsw.gov.au

Thanx
Ken