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
175 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now