When "Splitting" isn't enough

[Geogeek]

I've split my test run and most everything works great. However, how do I
provide a multi-user environment that does not allow each user to view/access
existing records (to prevent accidental data overwritting, etc.), but at the
same time allow the user to "Go Back" to a previous page (form) to make edits
while entering data?

I've performed all the trial-by-error methods that I can think of, in
addition to reading the "Access Bible", but I can't seem to get over this
last major hurdle. Please help before I hurdle myself over something very
high and steep!

Mucho thanks!!!

 

[Jeff Boyce]

We're not there. We don't know how your data is structured, nor how you've
organized/structured your forms for data entry.

It sounds like you want to prevent existing records from being modified, but
allowing existing records to be edited "while entering data". These seem
mutually exclusive.

Once you (Access) leave a form, the data is saved. Could you consider
working with a tab control, so the form isn't actually "left" when you go
from tab to tab?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Tim Ferguson]

I've split my test run and most everything works great. However, how
do I provide a multi-user environment that does not allow each user to
view/access existing records (to prevent accidental data overwritting,
etc.), but at the same time allow the user to "Go Back" to a previous
page (form) to make edits while entering data?

Number of approaches:

1) Install Access User-level security; remove all rights from users and
base all your forms on queries with RWOP set. These queries should filter
records according to "WHERE EnteredBy = [Username]" As long as the form
or the default values set the EnteredBy field correctly, they will be
able to see records they have entered and no other ones.

2) Have your users enter data into temporary tables, and then use an
append query when they have finished to push all the data into the real
tables. Remember to empty the temporary tables; or better create a new
mdb to hold them each time and delete if afterwards.

3) Avoid the problem altogether by educating your users about not messing
up other people's records. Read them the data protection act, or whatever
it takes. IME, human solutions always work better than technological
ones.

There are advantages and disadvantages about each approach. There are
bound to be other solutions too. Only you can decide what your business
needs are.

Hope that helps


Tim F

 

[Geogeek]

O.K., I'll try to summarize the essentials:

Data is structured into 18 tables, each containing specific properties (i.e.
drilling, construction, testing, etc) for a water well. All supporting
tables have been joined to the primary table using a db key number
(AutoNumber) in the back end only.

Forms, for the most part, reflect the corresponding table, and are joined to
each other using basic macros attached to events (AfterUpdate, OnOpen, etc).
For example, after entering data in the first form, the user clicks the
"Next" button which closes the current and opens the second form. This
structure is repeated for each preceeding form until the last form, which
returns the user to the main switchboard. The forms are joined so that I can
display the "Well Name" at the top of the form after it is entered in the
first form (purely for looks).

My primary objectives are the following:

1. Allow the user to enter data for a well with the ability to return to a
previous form to make a correction, or to even review (QC) what they have
entered;

2. Create a data entry environment in which the user can't accidently start
entering data into an existing record (overwriting?).

After splitting the DB and removing the navigation buttons in the front end
(to prevent access to other records) I've encountered the following problem:

Enter data for one well and close the program; then, if I open the front end
again, the forms displays the data I just entered, and is overwritten if I
enter anything new. I thought of using the "tab" approach, but I thought
that would require the data to be stored in 1 table, which didn't seem very
efficient. If I'm wrong about this, please let me know.

Any ideas??...thanks Jeff!

 

[Geogeek]

Tim,

I really appreciate your suggestions! I tried something similar to your
second (2) idea, except I couldn't figure out how to get around the issue
appending without the structure. Each record must have a unique DB key, and
my employer insists that it be auto-generated. As such, I can't append to
the back end (or something like it) because if I use an AutoNumber in the
front end, each record would have a DB key no. equal to 1. Likewise, if I
locate the AutoNumber in the back end only, it gets removed whenever a record
is appended. I tried getting around this by using the Lookup Wizard in the
primary table (this is before I split the DB) to a query that concatenated
two controls in the same table, but this won't work as this operation creates
a combo box.

I'm interested in your first (1) idea, but could you explain what a RWOP set
is?

Finally, in addition to the "overwritting" issue, I thought it would be good
to prevent access to other records since I'm using so many forms (18) for the
data entry. I didn't want the user to have to select (or make sure) the
correct well each time they opened the preceeding form.

Any more thoughts? Thanks Tim!!

I've split my test run and most everything works great. However, how
do I provide a multi-user environment that does not allow each user to
view/access existing records (to prevent accidental data overwritting,
etc.), but at the same time allow the user to "Go Back" to a previous
page (form) to make edits while entering data?

Number of approaches:

1) Install Access User-level security; remove all rights from users and
base all your forms on queries with RWOP set. These queries should filter
records according to "WHERE EnteredBy = [Username]" As long as the form
or the default values set the EnteredBy field correctly, they will be
able to see records they have entered and no other ones.

2) Have your users enter data into temporary tables, and then use an
append query when they have finished to push all the data into the real
tables. Remember to empty the temporary tables; or better create a new
mdb to hold them each time and delete if afterwards.

3) Avoid the problem altogether by educating your users about not messing
up other people's records. Read them the data protection act, or whatever
it takes. IME, human solutions always work better than technological
ones.

There are advantages and disadvantages about each approach. There are
bound to be other solutions too. Only you can decide what your business
needs are.

Hope that helps


Tim F

 

[Jeff Boyce]

One main form, one tab control, 'n' tabs (one for each table).

One form for each table.

Add each table's form as a subform on "its" tab in your main form.

The main form holds the main info, including well#.

The subforms (on tabs) are related, Child-to-Parent, to the main form's
well#.

(and I am unable to offer suggestions on your data structure, but there's
the possibility that your data would benefit from further normalization).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Geogeek]

Hmm...I think you may be on to something here. I'll do a test run and post
my results. Thanks!

 

[Marc]

My memory is a little fuzzy from when I did something similar a couple of
years ago. When you open the form, check the form property NewRecord. If it
is false, change the allowedit property to false. Put a button that will
allow the user to switch to edit mode if needed. Another possibility is to
put your form into a subform on another form and set the enable and lock
properties as needed. You also could lock each individual control on the
form depending on your needs. I hope this gives you some more things to try.

 

[Tim Ferguson]

I really appreciate your suggestions! I tried something similar to
your second (2) idea, except I couldn't figure out how to get around
the issue appending without the structure. Each record must have a
unique DB key, and my employer insists that it be auto-generated. As
such, I can't append to the back end (or something like it) because if
I use an AutoNumber in the front end, each record would have a DB key
no. equal to 1.

No: autonumbers are out so you'll have to write your own. There are lots
of possibilities:

interrogate the (real) backend for a new key number each time;

use a compound key using a serial number that increments for each
user with the user name itself -- this can be managed entirely
within the front end;

forget the keys in the front end, allow the back end to allocate
them at append-time. If you have a complex data model with lots
of foreign keys, then this is probably not a good idea!

One of my favourites is to use a combination of 1 + 2:

each FE has some database properties like NextSerialNum and TopSerialNum;
you can set this in the UI so it's easy; each user gets a widely
separated number range so as to minimise collisions. Try 120000 to
130000, 220000 to 230000 and so on. If your users are likely to insert
over ten thousand records, just space them a bit more! Long integers go
up to 4E9 so you should be okay for a bit.

at append time, you get the NextSerialNum and attempt to INSERT a new
record. If it succeeds, you use this record as your new record. If it
fails (unlikely, but someone else might have inserted a rogue somewhere),
you increment the NextSerialNum and try again. If it hits TopSerial then
you error out and tell the user to ask the Admin for a new chunk of
numbers.


There are lots of alternatives. Try googling for Access Custom
Autonumbers, but keep an eye out for multi-user safe versions.

One final thought: are you sure there are no natural keys already in the
whatever-you-are-storing that will a priori guarantee uniqueness? Just
because your boss wants an artificial key, it doesn't mean he knows what
he is talking about!

All the best

Tim F

 

[Carl Rapson]

In addition to all of the other ideas...

One thing you could try is setting a global flag (in the first form)
indicating that you are starting a new entry. Then, on opening each
subsequent form, check this flag - if it is set, allow the user to make
edits; if it is not set, don't allow edits.

Carl Rapson