Combo Box - Selecting Value

[Craig]

I have a staff list, of which some staff have left so I have marked them as
archived using a checkbox
On another form i have a combo box to select the staff member.

How do I display past records with the staff member name, but for new
records so it will not show the archived staff in the combo box

Staff Fields
ID, First, Last, Email, Phone, Archive

Form
ComboBox
Row Source Type: Table/Query
Row Source: SELECT Staff.ID, Staff.FirstName, Staff.LastName, FROM Staff
ORDER BY Staff.FirstName;
Bound Column 1


If i change the Row Source to:
SELECT Staff.ID, Staff.FirstName, Staff.LastName, Staff.Archived FROM Staff
WHERE (((Staff.Archived)=No)) ORDER BY Staff.FirstName;
Then the past records will not show its content.

How do I get around this.

Thanks
Craig

 

[John W. Vinson]

I have a staff list, of which some staff have left so I have marked them as
archived using a checkbox
On another form i have a combo box to select the staff member.

How do I display past records with the staff member name, but for new
records so it will not show the archived staff in the combo box

Staff Fields
ID, First, Last, Email, Phone, Archive

Form
ComboBox
Row Source Type: Table/Query
Row Source: SELECT Staff.ID, Staff.FirstName, Staff.LastName, FROM Staff
ORDER BY Staff.FirstName;
Bound Column 1


If i change the Row Source to:
SELECT Staff.ID, Staff.FirstName, Staff.LastName, Staff.Archived FROM Staff
WHERE (((Staff.Archived)=No)) ORDER BY Staff.FirstName;
Then the past records will not show its content.

How do I get around this.

What I've done is to add a textbox to the form, carefully superimposing it on
the text portion of the combo box. Its control source could be a DLookUp
displaying the name; the combo's rowsource would show only active staff.

The textbox will conceal the combo until the combo is dropped down. It's a bit
of extra work setting up the form but it works well for the user.

John W. Vinson [MVP]