Multiple users add info thru restricted form - How? (1 Viewer)

[Gunnerp245]

3/24/2009

I have "inherited" an Access database, because I have some training and can generally 'get' Access to do what I want, albeit, not efficiently. This Access database tracks the start and end dates of 'assignments' for many units (ships).

The database appears to be 'normalized' as there is no duplication of data in other tables and referential intergity is set. A naming convention has not been used i.e. qry, tbl, etc. I have also found 'reserved' words used as field names, which I have to correct, too. Neither of these are a 'problem' right now...but will be as I build the requested reports.

Currently one person updates all the 'assignment' start and end dates on an mainform "MRTINPUT" with a subform (MRTinput.jpg). The 'Assignment' table's main column 'Assignment' has the 100 unique assignments (AssignTable.jpg). The assignments themselves do 'not' change just their associated start and end dates for whichever unit with a particular assignment.

One person has to continually ask four different departments for data. I would like to allow one person from each department to update their own assignments' dates, BUT keep the data in the same table.

How would I restrict the individuals from seeing each others assignments?

I have read about splitting the database and incorporating username/password which I need to do once more than one person is entering data.

Thanks!
Gunner.

 

[DCrake]

Your logic is correct and you will need to split the mdb between FE/BE and introduce a user table. When the user logs in with a user name and password you can get you forms to filter out only records that belong to that persons department.

Your login screen should first identify that the user name is correct, then the password for that user is correct. Then it can determine which department the user belongs to. My only concern is that could a user belong to more than one department? If so you are going to have to decide on how you filter the records. You will also need to record which deartment the record belongs to when the record is created/updated.

David

 

[Gunnerp245]

...My only concern is that could a user belong to more than one department?David

The user will belong to 'only' one department. I want to keep all the data in the current table 'Assignments' and use only one input form "MRTINPUT".

The users (no more then 8) will all have edit capability but only to their department's 'assignments'. Therefore, once the user logs in and has access to the mainform "MRTINPUT", could a 'filter by form' function be used to limit the 'assignments' accessible?

Gunner...

 

[DCrake]

In a new or existing module create a public variable

Public LngDepartment As Long

Next Create a new Function

Public Function GetDepartment() As Long
    GetDepartment = LngDepartment
End Function

Save changes


Next design the underlying query that your form references and make sure that the filed DepartmentId is included.

Then in the Condition line under the column heading enter

GetDepartment()

Save your query

Next design you login form and in the part of the code that validates the user name and password enter the following line

LngDepartment = Me.DepartmentID

Save you form

Logic:

User logs in and the department Id uis passed to the public variable LngDepartment. When the user then opens a form that uses the modified query the query will call the function GetDepartment() which inturn will return the value of LngDepartment. Thus creating a filter on the query to only show records who belong to the department that the user has viewing rights to.

The above code has used field names that may not be the same as yours, remember to adjust the names accordingly.


Here is a link to sample database that comes with documentation illustrating the above techniques.
http://www.access-programmers.co.uk/forums/showthread.php?t=167718
David

 

[Gunnerp245]

Okay. I shall give it a go this weekend.

Thanks!
Gunner.

 

[gemma-the-husky]

However, in general restricting data in this way is hard work

Access is made to restrict data "vertically" as it where - you authorise users to have access to particluar tables/queries/forms etc - if they can see something then they can see it everywhere

To do what you are doing requires you to review every query/form etc individually, and write code that authorises some users to only see certain data, and other users to see all data.

So please be advised that although this is not particularly hard, it will be laborious, and quite time consuming to include it throughout your system.

 

[DCrake]

Dave.

It's a case of shutting the gate after the horse has bolted. If he had this in the original spec it would have been done during development but as it is an inherited one it is a big task. The original programmer is to blame here. Or it is a change in the spec that has activated it.

David

 

[Gunnerp245]

"...you authorise users to have access to particluar tables/queries/forms etc - if they can see something then they can see it everywhere. To do what you are doing requires you to review every query/form etc individually, and write code that authorises some users to only see certain data, and other users to see all data. So please be advised that although this is not particularly hard, it will be laborious, and quite time consuming to include it throughout your system.

I want them to only be able to change the data (assignments) for which they are responsible for. Changing the data means entering or changing the date. The users will 'not' be able to add or delete 'assignments'.

Dave.
It's a case of shutting the gate after the horse has bolted. If he had this in the original spec it would have been done during development but as it is an inherited one it is a big task. The original programmer is to blame here. Or it is a change in the spec that has activated it.
David

I hesitate to redesign the whole database, but if I have to I will. Maintaining the database is only one of my duties. Yes, it is an inherited database.
Gunner...

 

[gemma-the-husky]

its not so much it being designed that way intially, or even redesigning the whole thing

its more that functionality of this sort is non-relational, and really hard to build in through normal means

------------
HERE'S A DIFFERENT IUDEA WHICH MIGHT WORK

one way of doing it is to split all the data tables into departmental tables

say you have a table called

assignments

so split this into 4 tables, with separate info depending on the dept.

assignments_01
assignments_02
assignments_03
assignments_04

Now when a user logs in, relink the assignment table to depending on HIS department, so that his asisgnment table actually points to say assignments_03.

now everything works normally, but he can only see things for dept3.

----------
the only thing you lose is the ability to have a management login, to see all the data. You could achieve that by giving them a special union query to unite all the data - this would be nonupdateable, but thats probably a good thing

its probably MUCH quicker to do it this way, than rewrite the whole app.

is this idea any use?