Protected data entry

[D Toole]

Hi,

I have set up a database to track the progress of our
machines. This is a 12 step process. Currently, the
information is being gathered on a data sheet by one
individual and entered into the database. 95% of the
information is stored in one table with the exception of
look up fields. Reports are then generated from queries
of this information. So far this is not a problem. I am
wanting to go to a multi user enviroment where the
machine's status will be entered as it moves from one
station to the next. This can be obtained through user
level security to some degree but not entirely. I want
each user to be able to enter information in empty fields
but not be able to alter existing information in other
fields of the same record. I have one field which must be
able to be updated by every user. I have isolated this
field and the primary key to its own table (hoping to
grant full access to this table alone). The field which
must be updated by every user (Machine Status) is a field
obtained from a look up table. I tried writing a macro
which would automatically set the value of (Machine Status)
to the correct value when data was entered in the
appropriate field. (Ex. When the Burn In date is entered I
want the Machine Status to change to Burn In. Later when
a Break in date is added, I want the Machine Status to
change to Break In.) Access will not run this macro. It
will not let me use the set value command in this manner.
My guess is because the field I want it to alter is
obtained from a look up field.

My reports use the (Machine Status) column to group and
total like values.

I have tried user level security but New Data Users
pertain to new records not empty fields within records.

This problem is preventing me making strides with this
database.

I would much appreciate any help someone might have to
offer.

 

[Tim Gould]

Do all the data entry through a form. I suspect that not
allowing editing won't work - it sounds like there is a
record for each machine and data is added to the fields as
the process passes from user to user. Adding data to a
field in an existing record counts as editing, even if the
field was previously Null.

You could separate the form into a number of subforms.
Then create security groups for each stage of the process
and give each group access to the appropriate subform. But
this is fiddly and you'd need to keep changing it
everytime the process changed.

Much easier (IMHO) would be to put a routine on the
Before_Update event of each field. This routine should
pass the old value of the field and the new value to a
function. The function should examine the old value; if it
is "Null", then it allows the new value to be entered in
the field. If the old value is other than null, it does
not allow the update.

You can also add to the relevant fields code to change the
Machine Status if the update was allowed.

I think it is better to call a function to do this because
it will be easier to maintain (imagine having the
procedure duplicated for every field).