Records on linked databases only show on another based on a condition.

[tweeter85usn]

I'm not sure if I am posting this in the correct area, and I appologize if it is not.

I have multiple databases that are linked. They are all identical. The main database is to be used by administrations department and need the information from all records. The other databases are for other departments that need only the information for there department. I need to be able to ensure that each department can only access the information they are privileged to.

For example I have MainDatabase.mdb linked to DatabaseAlpha.mdb and DatabaseBravo.mdb and so on. In MainDatabase.mdb a record has the field named Section populated with ALPHA. I want DatabaseAlpha.mdb to see the record but not DatabaseBravo.mdb. If Section is populated with BRAVO then only DatabaseBravo.mdb will be able to see the record.

I have looked everywhere I can think of for an answer and have not been able to find anything even close. If this can not be done then I understand.

 

[Pat Hartman]

You can create a table that defines the department for each user. Administrators would have no department defined. When the database opens, lookup the user in the user table and save the department on a hidden form. Then every query would include criteria that referenced the department field on the hidden form. The following shows how to make the administrators access all departments.

Where (Dept = Forms!frmHidden!txtDept or Forms!frmHidden!txtDept is Null) AND any other criteria the query needs.