Solved

An error message WILL happen....but I want to find it before I try and save the record.

Posted on 2004-10-06
11
182 Views
Last Modified: 2012-06-27
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    MsgBox "An error occured:  " & DataErr
End Sub


Gives me:

An error occured:   7787


then I get a Write Conflict error.


================================

The problem is..........I want to find out that I am going to get this error message SOONER rather than LATER.


Is there  a way to force the error message to pop-up (which normally happens when I try to save a record that another user has edited) without having to save the record?





0
Comment
Question by:knowlton
  • 7
  • 4
11 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 12243458
Hmm, wy not try to prevent the message from occuring anyway ?

By adding a datetimestamp "LastEdited" to the table you can read the record just before the update and detect the change by the other user on forehand. All needed is to save the datetimestamp when filling the form.

Clear ?

Nic;o)
0
 
LVL 5

Author Comment

by:knowlton
ID: 12243568
So....User A edits a field on the form..............when this happens  (form is dirty).......the LastEdited field gets updated with a timestamp value of   "10/6/2004 4:48 PM"    User ID :    UserA.



Now....User B wants to view the same record.............User B sees that the LastEdited    field    has a value.


So what?   I guess I don't understand how this helps me.
0
 
LVL 5

Author Comment

by:knowlton
ID: 12243579
Are you saying to do a DIFFERENCE on the value in   LastEdited    versus    Now(  )    ????
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 12244013
No, a difference between the actual DateLastEdited on the bound form and the value of the row you collect before the UPDATE is triggered.
Sample:
Assume rowX is LastUpdated on 12/12/2003
1 UserA opens rowX to update -> No action and field on form is 12/12/2003
2 UserB opens same rowX       -> No action and field on form is 12/12/2003
3 UserB updates X                   -> Row's DateTime gets changed into 10/06/2004
4 UserA tries to update X         -> Compare DateTime from Read row with form will fail...

Clear ?

Nic;o)
0
 
LVL 5

Author Comment

by:knowlton
ID: 12244079
Actually...yes....I understand it now.

===============================

But why put myself through this?

WHY can't I just ask the record if it is locked or not..........ANY time I want to?????????

It seems so simple to me........why is it so hard?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12244143
Access will get the message from the Database engine when trying to update the row. The database won't be aware of the update intent untill the user saves the row as (s)he could abort his update action by cancelling the update.
Normally I add a Reset button on an Update form, thus allowing the user to return to the initial values using the:
Docmd.Undo
This will however only show the fields as "copied" when the form was opened and no intermediate changes will be visible.
To get these visible you'll need to re-read the row.

Nic;o)
0
 
LVL 5

Author Comment

by:knowlton
ID: 12244215
The whole point of what I am trying to do is to NOT WAIT until an update attempt is made.....but to find out as soon as the LOCK happens.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 12244287
As stated in the other question, you'll run into the "lost/forgotten locks" problem.
The described method here is used in general in the banking branch to prevent the occurrance of concurrent updates.
As the time between the second read to get the latest datetimestamp and the update is a split second, this provides an almost 100% secure way to prevent data loss.

Nic;o)
0
 
LVL 5

Author Comment

by:knowlton
ID: 12250250
Understood.


Now....the  "LATEST DATESTAMP"        versus    the     "LOCKED ON"    "LOCKED BY" solution you spoke of in the other post are the different ways of solving the same problem?????

Or are they diffferent?
0
 
LVL 5

Author Comment

by:knowlton
ID: 12251568
My interim solution based on feedback from Nic;0) and other experts:

http://www.robotzgame.com/junk/CLP.rtf
0
 
LVL 5

Author Comment

by:knowlton
ID: 12251684
http://www.robotzgame.com/junk/CLP.zip

is MUCH smaller....hope I caught everyone in time.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question