Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-06-17
5
Medium Priority
?
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 31

Accepted Solution

by:
hnasr earned 375 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 31

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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