Hide Selection Option on form by making lines on a table INACTIVE

[jurbin]

I have several tables linking our employees to certain supervisors, etc. On the Form, there is a drop down with all employees listed that someone can select and then enter the stats required. When someone leaves the firm, I want to remove them from the form dropdown list, but not delete them from the corresponding table. I have seen this in other databases where they someone added an 'Inactive' column with a Yes/No response and when you selected the inactive - yes, that name would disappear from the form when people select names from a dropdown to enter stats.



Can anyone help me figure out how to make the names disappear from the view in a form, but not in the table related to that form?

[June7]

As you said, filter the records based on status. The form RecordSource could be like:

SELECT * FROM Employees WHERE Status="Active";

The same can be done for a combobox RowSource.

SELECT EmpID, EmpLast & ", " & EmpFirst AS EmpName FROM Employees WHERE Status = "Active";

[jurbin]

As you said, filter the records based on status. The form RecordSource could be like:

SELECT * FROM Employees WHERE Status="Active";

The same can be done for a combobox RowSource.

SELECT EmpID, EmpLast & ", " & EmpFirst AS EmpName FROM Employees WHERE Status = "Active";

I think that would be done using VBA or SQL or something?? Is there a more simple way within the database if you dont know VBA?

[June7]

Not suggesting VBA. Those are SQL statements that would be in the respective properties (RecordSource and RowSource) of form and combobox. Basic Access functionality.

[jurbin]

Thanks I just got that...So I figured out where the row source is in properties for my form. the following is the current SQL statement

'SELECT [Employee_tbl].[Employee] FROM Employee_tbl ORDER BY [Employee]; ' What that does is gives the list from my table of employees and orders them in ABC order. I tried to add on something like 'WHERE 'Inactive' = 'False' It doesnt work though, it gives me a couple different errors on the form.

See on the employee table I have a column titled 'Inactive' and its a checkbox for yes/no or true/false. I believe mine is listed as true or false. But for some reason it will not let me add that part of the SQL statement at the end. Could you think of the formula I would have to add on in order to eliminate the inactive people?

Thanks so much for your help already. I really appreciate it.

[jurbin]

I actually just got it to work! Thanks so much for your responses, wouldnt have came close without it.

[June7]

SELECT Employee FROM Employee_tbl WHERE Inactive = False ORDER BY Employee;

Field names should not be enclosed with quote or apostrophe marks.

Also, True and False are boolean constants known to Access and should not be enclosed when used as boolean. Same for Yes/No. Or use -1/0 as boolean parameters.