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

JamesSF wrote:
>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.

--
Marsh
MVP [MS Access]

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> JamesSF wrote:
>>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.
>
> --
> Marsh
> MVP [MS Access]

JamesSF wrote:

>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.

--
Marsh
MVP [MS Access]