Global hide of entries on one fields criteria

[avayaman]

Is there anyway to activate a global hide of all records in a database according to the value in one field? I would like to be able to toggle this on & off with a button or 2 on the switchboard.

This is a a database of members in a non profit org. Rules state we have to keep them all on file even though they are no longer active. These ones show up in a status field as "Archived" I would like to have a way to opt to make them invisible for day to day usage of this database. They will still be there, but not show up in the roledex feature or as individual entries. They already show up in faint text, that was no big deal.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[missinglinq]

The standard way of doing this would be to use a query (based on your table) to base your forms/reports on. Then in the Criteria box for your field Status enter

<> "Archived"

Now only records without the status Archived will show.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[avayaman]

I can do that but there are tons of queries. I was looking for some sort of universal process. There is one master table everything is based on, maybe I can do something with that.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[MajP]

1) I would see if I could fix all queries as missingling suggests. If tons is less than 100 then that may be a few hours of work. If tons is a thousand then you may want a different strategy.

2) If the users only interact with forms and reports (proper design) and not tables and queries (improper design) then you can pass a global filter string to all reports and forms. Now if there are thousands of queries, but only tens of reports/forms this is a doable strategy

3) If you do not work with archived records on a daily basis the simplest solution would be to move them into an archived table and delete from the main table. Now you might have to build some additional forms and queries to interact with the archived results. I assume you have a split datbase with the tables in the backend and the forms, reports, queries and code in the front end. I would move my archived results into a seperate table in a seperate database and link to both backends. I assume only certain users deal with archived records.You could actually moved archived records into a seperate backend. This would then not affect any existing queries.

 

[missinglinq]

You also have to wonder about normalization, I think, when "tons of queries" are all being pulled off of one table.

But at the table level, I think MajP's suggestion of moving the archived records to a separate is the only solution available.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[avayaman]

Hmm, I will give some of those suggestions ago. In retrospect it would have better to create one large query mirroring the main table & have based all other queries on that rather than the master table.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[MajP]

That is not a bad suggestion.
1) assume your main table is called "tblMain"
and you build a new qry called "qryMainActive" with only the active records but all fields.

2) then in code you could simply read all the query defs and replace all instances of "tblMain" with "qryMainActive'. That would only take a few lines of code. Back up the database before trying it.