Solved

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

Posted on 2008-06-17
5
410 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

825 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