Multi user form: The data has been changed

[Vinod]

Hi All,

I've developed a bound form in Access 2003 with country and product
dropdowns. On selection of country and product dropdown values the
corresponding stock details with order number, quantity and unit price gets
displayed in the form. Finally we placed the access .mdb file in shared
location.

Now the database from shared location is opened by two users in two
different systems. Two users selected same country and same produt id. One of
the user modified unit price for that selected product and that has been
changed in the table.
But the modified Unit price is not refreshed in 2nd user's form. When 2nd
user tried to select another product from produt dropdown then 2nd user
getting the following message on click of 'Ok' button the modified unit price
is refreshed but product dropdown still displaying old selection.

Here is the message box info:
The data has been changed.
Another user edited this record and saved the changes before you attempted
to save your changes.
Re-edit the record.

Here is my question, I don't want the 2nd user getting this message and the
product dropdown should select with selected value from dropdown followed by
refreshing/requery the form with selected dropdown values.

It would be appreciated if any one can share your thoughts or ideas to give
workaround for my question.

Advanced Thanks
~Vins

 

[John W. Vinson]

Hi All,

I've developed a bound form in Access 2003 with country and product
dropdowns. On selection of country and product dropdown values the
corresponding stock details with order number, quantity and unit price gets
displayed in the form. Finally we placed the access .mdb file in shared
location.

Now the database from shared location is opened by two users in two
different systems.

This is a guaranteed way of getting user contention (what you're seeing),
database bloat, and irreversible corruption of your database.

A multiuser database MUST use a "split" architecture: the Tables in a shared
backend, with each user having their own individual copy of a frontend,
containing the Forms, Queries, Reports and code, linked to the tables in the
backend.

See http://www.granite.ab.ca/access/splitapp.htm for a thorough discussion.

 

[Vinod]

Thanks Vinson for your response.

I'm planning to split thed database one with only tables another with
queries, forms, macros and linked tables from split database. This helps me
in data integrity when front-end database gets crashed.

Eventhough I need to place two databases (backend & front-end) in shared
location so all the users will open fornt-end from shared location only. With
this situation I don't want to get message which I posted from my orgional
post.

It can be appreciated if I get the solution as mentioned.

 

[John W. Vinson]

Eventhough I need to place two databases (backend & front-end) in shared
location so all the users will open fornt-end from shared location only.

That is the WORST POSSIBLE setup.

It will give you all of the disadvantages of a shared frontend (contention,
performance, bloating, risk of corruption), and all of the disadvantages of a
split database (mostly performance).

Reread the links about splitting. The *basic* principle of splitting is that
there should be one shared backend, and MANY copies of the frontend, one for
each user.

If need be, you can have multiple copies of the frontend in private folders on
a shared disk... but you *MUST* have multiple frontends.

With
this situation I don't want to get message which I posted from my orgional
post.

What do you WANT to happen when two users are attempting to edit the same
value, in the same field, at the same time?

Jane wants to increase XYZ in record 123 from 10 to 15.
Bill wants to decrease XYZ in record 123 from 10 to 8.
You don't want either user to be warned that the other user is trying to
change the record.

What do you want Jane and Bill to see?

 

[zarga62]

Thanks Vinson for your response.

I'm planning to split thed database one with only tables another with
queries, forms, macros and linked tables from split database. This helps
me
in data integrity when front-end database gets crashed.

Eventhough I need to place two databases (backend & front-end) in shared
location so all the users will open fornt-end from shared location only.
With
this situation I don't want to get message which I posted from my orgional
post.

It can be appreciated if I get the solution as mentioned.