Noob - Partially locked forms, and general database design questions.

[StolenDog]

Hi there



I am trying to set up a simple database that will be used by six or seven staff members simultaneously. I am using Access 2007 on a network drive (windows XP).

The purpose of the database is to have one user (administrator) be in charge of assigning set tasks through a form to (clerical staff). The clerical staff will then go in to their own form to complete the task by updating certain fields such as date completed and any follow up etc. Both forms should link to a Table where the details are stored.

So far my database includes the following:

Table for products
Table for actions (i.e. what does the clerical person need to do)
Table for clerks names, ID, etc.
Table for tasks (this is the main table that includes fields from the other tables, product, actions, and clerk name, assigned date, close date, etc.)

I have set up two forms, both are set up with the form wizard and link to the Table for tasks the form includes a unique number (auto populated), a member id (text), a product (combo box linked to table), an action (combo box linked to table), a clerk (combo box linked to table), an open date (todays date), a close date, and a notes field.

The point of this exercise is to develop a tool to track the status and location of open tasks, we process a lot of paperwork and need to know where documents are and what the status is. This system used to be in Excel but it just got too big to keep in excel, we generally accumulate 40,000 rows (tasks) per year.

The general requirement are:

1. The clerical staff cannot change any of the detail of tasks assigned to them, other than the close date and comments.
2. The administrator cannot close the tasks, or edit the comments.
3. Both administrators and clerical staff can access the forms at the same time, and updated at the same time.

My questions are:

1. Is there anything wrong with my logic in he way I set his up? I.e. is there a better way to do this?
2. How the heck do I set up a form or table that allows multiple users to edit certain fields, but not other fields, i.e. Administrators have access to everything except the close date field, and comments field, and clerks have access to only close date and comments?

What I have tried so far hasn't worked for example in the Administrator form on fields I want the administrator to be able to edit I set the "Locked" to no, and "Enabled" to yes, on the Clerical version of the form I did the opposite, by setting those fields to Locked yes, and Enabled no. What happens is no one can enter anything now, there must be a better way to do this, can anyone help?

Thanks
A

[ranman256]

I have a tUser table, userID,name,level.
the level code is A,admin;M,manager;null ,reg user.

when the user opens the db, the main form captures their userID,
vUser= Environ("username")

then I lookup their level,
vLevel = Dlookup("[level]","tUsers","[userId]='" & vUser & "'").

now that I have both I can enable/disable fields or forms...
txtBox.enabled=(vLevel="M") or (vLevel="A")

[StolenDog]

Hi ranman - I like your avatar, that's a great movie.

Thanks for your feedback. So if I understand correctly, in non-access language what you are suggesting is to give users different levels of authority and based on their authority the will be able to access certain parts of forms or fields?

I think in order to do that I would have to understand how to build login or authentication screen etc.? I don't know how to do that, as it stands anyone can access my database. It's really limited, I don't have a login screen, I was planning on just hiding the tables etc..

maybe you could suggest some reading material or how to guides?

Thanks

[ranman256]

You don't need the login screen ,the user is already logged Into windows.
that is the id used.

[StolenDog]

Okay I see, but how do I tie the user in access to the company database?? That sounds like it would be beyond my level of influence, I don't even work in IT...

Are there any other suggestions you could make that don't involve a connection between the users access rights and what they can see on the DB?

Thanks
A

[ranman256]

you could make a table to hold a list queries.
qsAllRecs, A
qsMRecs, M
qsURecs, U (for normal users, instead of null)

then after looking at the user Level, only allows these queries to open.

[Missinglinq]

...I am trying to set up a simple database that will be used by six or seven staff members simultaneously. I am using Access 2007 on a network drive (windows XP)...

...but how do I tie the user in access to the company database...

I suspect that the answer to the second quote is directly related to the first quote!

It sounds suspiciously like you are you trying to have all employees use a single, non-split copy of this database that resides on the network drive? Is this the case? If so, you need to address this before you do anything else!

Having multiple users sharing a single, non-split database, sitting on a network drive, is the sure way to repeated episodes of corruption, as well as speed and timing problems! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past ten years have seen dozens and dozens of reports of non-split apps causing these kind of problems! The really insidious thing is that a non-split app can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

Once each user has their own copy of the Front End, pulling their individual Windows ID will be a snap...and the business of no one being able to do anything will probably be resolved!

Here are two tutorials on Splitting a Database:

https://www.fmsinc.com/microsoftacce...abaseSplitter/

http://www.hitechcoach.com/index.php...atid=24:design

Linq ;0)>

[StolenDog]

Thanks Linq and Ranman

You were right. I was planning on deploying a non-split copy of the database. I read through the instructions on the first link you provided and it seems relatively straight forward to split the database I've designed. I'll do that and then check to see that it still works. I'll get back to you about how to pull individual Windows IDs and other things...

Thanks
A

Update -

Okay I've split the database. That was easy.

So if I understand correctly I can now just make 6 copies of the front end (forms) and go into each front end and modify the master form to include the fields and forms I want for each user??


Thanks again this is a big help.