Solved Can I completely hide (but not remove) a record in my database.

[Khurramshaikh47]

Hi, I need help about record hiding in Microsoft Access database.
I have an employee database, including employee attendance, payroll etc. When some employees resigned and left the company how can I hide them in attendance forms, payroll forms, and reports etc. I dont want to remove. I want to keep their history in my database. But I dont want to see them in any report or form.

Please guide me!
Thank you!

 

[NauticalGent]

Give this thread a look over...

How to handle “inactive” records?

Dear all, I am just a beginner with access. Therefore, please apologize if I am not yet familiar with most of the Access functions. My situation: I am currently using Access 2016. My main purpose is to have a simple Form-Subform structure. This means for example, having a form with a user...

www.access-programmers.co.uk

This post in particular may be what you are looking for.

 

[bob fitz]

If you have a "DateOfLeaving" field you could use that in Form/Report queries to filter the appropriate records.
Alternatively, you could add a yes/no field to your employee table called something like Active and mark each record appropriately.

 

[gemma-the-husky]

Yes. You don't want to delete data that you might need, and you took a lot of effort capturing in the first place. The database needs to be able to distinguish between active and inactive rows - in the same way you might want to extract details for employees over a certain age. You don't delete the ones you don't want. You just frame the query appropriately.

You aren't hiding the row in the table. You just are selecting it out in the query. Users should never see the tables, so it shouldn't matter.

 

[The_Doc_Man]

I want to expand on Dave's comment "Users should never see the tables." (Dave uses gemma-the-husky for a screen name.)

First, if the users have direct visibility to the navigational aspects of the DB, you cannot hide anything. This is why you should look on this forum for the topic of "Securing a Database." You will learn that users must ONLY directly see forms, never tables or queries. Forms and reports are the only way that users should see ANYTHING. It's sometimes a bit of work but when you secure things properly, your users will be limited to only seeing what you want them to see and nothing else (unless they are hackers).

IF you have forms set up to view records, one of the fields in the record can act as a signal. So if you base the form on a query that filters out what you don't want seen, nobody sees it. That signal could be that there is a termination date (which active employees don't have) or a status code that says active or inactive or whatever names you have for the statuses. The latter could be a Yes/No for "ActiveEmployee" if that is all you needed.

 

[Pat Hartman]

Use a TerminationDate and perhaps TerminationReason. Yes/No flags give you no useful information and are redundant when you have useful information so avoid them.

Your maintenance form should not automatically filter by TerminationDate because you might rehire a person or he might come back from leave. If you have a lot of terminated employees, you might have an option on the form to show All, Terminated, Active and let the user choose.

All the other queries should only include employees where the TerminationDate is null.