Solved

How to update a current record in code, and avoiding write conflict with the datasheet record.

Posted on 2008-06-17
5
406 Views
Last Modified: 2012-06-21
I currently have a Access 2003 application that has some strict design parameters.
Up the top of the screen is a datasheet showing each record, and it is editable directly into fields.
At the bottom is a series of tabs, with the same fields as above divided among them.
An update in any field will update the corresponding field.

My issue is that I have a table with a 3 field key.
The user always gets a 'primary key error' as they try to enter in new values for the key fields.  Obviously a user cannot simultaneously enter 3 fields at the same time. I dont want the error message appearing to the user.

My solution was to have a small popup, when a key field got focus which allowed the user to enter all 3 fields on an unbound popup, then I would save the the key via CurrentDB.Execute. It seemed to work okay until i then went to another non-key field or changed records. I then got the 'Write Conflict' error..argh.

Can someone give me some direction on how to tackle the entry of a multiple field key into a datasheet view.  (sans error messages)
Please dont suggest an Autonumber or ID field as the solution, as it is not an option.
0
Comment
Question by:Milkus1
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You get the error because you are 'independently' updating the record with the Execute command'.

However, you should not get that error ('primary key error' a) *until* you actually try to Save the record, so it's not clear why that is happening.  Basically, you enter the first field - the record becomes dirty. You enter the other two - plus any other fields ... and the you Save the record - either via a button or by tabbing out of that row. So ....??

btw ... an AutoCounter is *always* an option, because you can still create a multi-key Index on those three fields and set the Unique property to Yes (and Required to Yes or No - depending).  This would probably resolve the 'primary key error' ... but of course, those three fields would need to be unique - which is the case now
0
 
LVL 30

Accepted Solution

by:
hnasr earned 125 total points
Comment Utility
Not clear, why it entering a value in part ofthe key updates other parts of the key.

Chek the indexed property for each part of the primary key. Each should allow duplicates.
0
 

Author Comment

by:Milkus1
Comment Utility
I know why the write conflict occurs, and a ID field is not possible due to the regular importing of 'legacy' data from different clients. I dont wish to make another big problem by changing the table structure...if I can solve this.

When I look at the three key fields, the first is indexed and allows duplicates, and the other 2 are not indexed.
I have followed hasnr advice, but do i need to set them to 'Required-Yes' as well?
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Milkus1,

Not necessarily for required, whole key is required anyway!
No apparent issue with the 3 part key.

Next step:
Can you reproduce the problem with a new database with general test data?

If you can reproduce the problem then
       attach database to look at
end if
0
 

Author Comment

by:Milkus1
Comment Utility
The issue seems to have lied around the Child/master linked fields between the datasheet and tab.
The linked fields where a concatenation of the 3 fields together (forming a single link), which was fine if all key fields existed but where they had not been inputed, things fell over.
I made the linked fields the 3 distinct key feilds and though I am still chasing bugs...the write conflicts have subsided to something manageable.

Thanks all for your input, though it was directly answers ...your prompting made me think a little deeper about what was happening.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

10 Experts available now in Live!

Get 1:1 Help Now