Combo Boxes and dynamic drop downs

[JamesSF]

Hello

I have a subform in datasheet view that has a list of people that have
worked on a project.

Some of those people are inactive or no longer employed. I want to see who
has worked on the project but i dont want people to be able to add inactive
or retired or no-longer-emplopyed people to the list.

Whenever i try to change the comboxbow rowsource to exclude inactive people
(via a text flag in the table), it removes the names of the past people.
(primary key is saved in the table and the combobox shows the displayname).

how can see the historical names and limited new names to active only from
the combox in a datasheetview form?

thanks!!
jamesSF

 

[Marshall Barton]

I have a subform in datasheet view that has a list of people that have
worked on a project.

Some of those people are inactive or no longer employed. I want to see who
has worked on the project but i dont want people to be able to add inactive
or retired or no-longer-emplopyed people to the list.

Whenever i try to change the comboxbow rowsource to exclude inactive people
(via a text flag in the table), it removes the names of the past people.
(primary key is saved in the table and the combobox shows the displayname).

how can see the historical names and limited new names to active only from
the combox in a datasheetview form?

The word "historical" implies a date when the person became
unavailable to work on the project. Maybe your text field
should be a date field so the form's record source wwould be
able to filter out people that left before the project
started. You could then also use code in the form's
BeforeUpdate event procedure to prevent new records from
being added when the not available date is already set.

 

[JamesSF]

ok thanks - that works - got me on the right track

is there a better way besides doing a dlookup?

on the before insert, i have it check the table for that person to see if
they have been flagged inactive, if they have, i can cancel and undo the
selection and msgbox the user

JamesSF

 

[Marshall Barton]

ok thanks - that works - got me on the right track

is there a better way besides doing a dlookup?

on the before insert, i have it check the table for that person to see if
they have been flagged inactive, if they have, i can cancel and undo the
selection and msgbox the user

DLookup is easier and just as fast as any other way. If
speed is critical and if each person is a unique record in
the table, you might(?) find that:
DCount("*", "table", "personID= & Me.personID & " AND
inactivedate Is Not Null")

is a little faster, especially if both THe PersonID and
InactiveDate fields are indexed.