Creating a Drop Down List From Table Fields

[Theman]

I have two fields in my table called Act1 and Act2. These two fields will frequently have the same personnel names. On my form I would like to create a drop down box that displays the info from these two fields so that when a user adds a record, he can select from the list and not have to type it in. What is the best way to do this and to filter it from duplicates? Thanks.

 

[scottk]

You could do several things.

Create a new table, which you may wish to call Personel or something along those lines. Add all the possible names to this table.

Then on your form create a combo box for Act 1. Follow the steps in the wizard to link the combo box to the Personel table and store the data in the appropriate field of whatever table your form is based off of.

This is the best way to create a data lookup field. However, you can skip building the table if you wish, as the combo box wizard should provide you with the option of entering values to appear in the combo box. This second method may be a little difficult to add or subtract names as your personel may change over time though.

What sort of duplicates are you wishing to avoid? If the same personel is in Act 1 and 2 then the fields would naturally contain the same data. Let us know if we can provide any more assistance.

 

[Theman]

Hi Scott,

Thanks for the response. The field Act1 could have a name or names from Act2 and vice-versa. I had thought that if I somehow linked the two fields that duplicates would appear and I would need to filter them out so instead of say Smith appearing three times as it does between those two fields, it would only appear once. I had hoped to avoid creating yet another table however, if that's the way to go then so be it.

 

[Rich]

Why does your table have the same name in two seperate fields?

 

[Theman]

Act1 is main employee and Act2 is backup employee. These positions frequently switch.