• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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

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
Milkus1
Asked:
Milkus1
  • 2
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
hnasrCommented:
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
 
Milkus1Author Commented:
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
 
hnasrCommented:
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
 
Milkus1Author Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now