View all records in form view, unless record has a specific value in a specific field.

[justcop]

Hello all,



I am brand new to using access so please be gentle! I just started trying to learn how to use it yesterday, having said that I am fairly technically literate and am reasonably familiar with mid level complexity excel spreadsheets, so I am hoping I can pick things up quite quickly. Now my question:

I have a table which includes the details for a list of patient data which we have collected. This information must be easily added to and edited by clinicians in the form view. I already have a beautiful form which allows that to be possible. What I am trying to do is have a tick box on the form which allows the data to be removed from this view.

The complexity (at least for me!) is that once a record is deleted, I want to keep a record of it.

It seems to me that the best way, would be to have the main table of patients both current and deleted, there would then be a field within this that would state whether they were current or deleted. What I would like to know, is how I can then have a form view that just includes only all the current patients, so that clinicians can see all the relevant data in the front end, but I retain all the information in the backend.

I look forward to hearing some suggestions, I'm fairly sure the answer is pretty easy. Thanks in advance.

Justcop

[June7]

Yes, table with field - perhaps a Yes/No type and named IsCurrent.

Set the form's RecordSource to a query object (or SQL statement) that has filter criteria, something like:

SELECT * FROM tblPatients WHERE IsCurrent=True;

[pbaldy]

You can have a status field and filter the form on that. Base the form on a query that includes the appropriate criteria to only include the desired records. The status field can be a yes/no field, or a numeric/text field if you want to have more possible statuses.