Solved

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

Posted on 2008-06-17
5
407 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
ID: 21809031
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
ID: 21809315
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
ID: 21810404
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
ID: 21810519
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
ID: 21871101
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

11 Experts available now in Live!

Get 1:1 Help Now