I am working on a database that my site has used for years. My database has drawn the attention of the bigwigs because of its accuracy, and the company wide system has not been able to do the same things that I can do with my database.
The "bigwigs" want to use it corporately. They want me to maintain the database, at my site, but use it throughout the state. I was wondering if there was a way to sort information by site when a user logs in, and I want the information only to show their specific site. I don't want to have to make a new query or form for each site that lists only what they have access to. I would like to automatically filter the information based on their log in information. Is this possible?
I've done something similar with a departmental split. Which was done as follows:
Create a table tblUserDepartment with two fields as dual primary key. One to contain a predefined UserName (as set up through MS Access security) and the other field named DepartmentName. Associate the users to multiple departments by creating records in this table if neccesary.
Create a field called DepartmentOwner in your table of data tblData which is defined by the user on creation of the record. On the form used to view the record(s) Display the current user of the database with an unbound field with a control source =CurrentUser() and name it 'CurrentUser'
Base your form used to view the records on a query which should consist of tblData and tblUserDepartment with a relationship on DepartmentOwner and DepartmentName. This will duplicate rows in the query i.e. one record of actual data x number of users listed against the department initially. To display only one line of data to the user on the form, enter a reference in the query design grid to the UserName field as [Forms]![frmFormName]![CurrentUser] which will pull things back into sync.
There may be an easier way but it works fine for me!