How do I add a record and ComboBox list gets smaller

[cindy007]

How do I reduce combo list size after selecting a record.

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?

Cindy

 

[Mile-O]

The first thing I'd say, before going any further, is to rethink your table structure as having repeating groups as field names (i.e. Bay1, Bay2,...Bayn) is going to, and will, cause you complications in the future.

You appear to have designed your database table to be more in line with an Excel spreadsheet which you shouldn't be doing as they are two different applications.

You would be better suited to have your table structure reduced to this:

tblRoster
RosterID
PositionDate
EmployeeID
PositionID
BayID
Other Roster Details

where:

TableName
PrimaryKey
ForeignKey
TableField


The fields EmployeeID, PositionID, and BayID would relate to three other tables which held Employee details, Position details, and Bay details respectively.

To understand why this is a better design, please research normalisation, normalising, or, with the other spelling: normalization; normalizing.

 

[bjackson]

Cindy this may give you some ideas
there is no error trapping in the code but its a simple way of doing what you want

 

[cindy007]

Shrinking ComboBox List

bjackson,
The zip file you have attached does not seem to contain any data, is anybody else having a problem accessing the contents of the attachment?

Mile-O-Phile, Thanks for the tip !!

Cindy

 

[bjackson]

dont know what happened there
il try it again

 

[Mile-O]

Once you download the attachment rename it to attachment.zip - it would seem that it has lost its extension.

 

[cindy007]

Attachment

Thanks for the help guys, I got it to work this time.. BJackson, that looks pretty good, I like the idea.
Once again, thanks guys, for your time and assistance, you have opened my eyes to a better way of approaching my problem.

Cindy