Write Conflict (the whole enchilada)

[MinnKota]

Here is a detailed discription of my problem: I have Two tables (Authors, Titles) that are joined with a one-to-many relationship. I use a query to use these tables as a basis for my form. My form has three text boxes that I am dealing with right now FirstName, LastName, and FullName. The user fills in the First and Last names and I want the Full Name to be updated in code... and here is my code

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.FullName = Me.FirstName & " " & Me.LastName
End Sub


This works great for new records, but when I edit a record (i.e. change the last name) and then try to save it. I get a WRITE CONFLICT error. I have read through the write conflict posts and there seems to be a reference to docmd.runcommand accmdsaverecord, but I have not been able to get it to work. Any suggestions?

 

[MarkWright]

Hello,
I just made a small db with 1 table using your name fields. I made 1 form with all 3 fields and used your coding. It worked for me. Check your form if allow changes is set to yes.
Are the 3 fields in one table only or is something being saved in both tables like the full name ??

Mark

 

[MinnKota]

Mark,

The test database you setup is quite different than mine. I have also setup a database with one table and it works, but with two tables and a form based on a query...no luck.

Christopher

 

[cmmrfrds]

Is your program and Access Project (adp) or and Access MDB?

 

[MinnKota]

MDB

 

[cmmrfrds]

Access allows combining multiple tables in an updateable recordset (called dynaset, or inconsitent dynaset). The inconsistent dynaset is looser in its rules and allows some updates that would be restricted in a dynaset. In your case, probably what is happening is that one of the tables does not have a primary key. You can insert without a unique, but of course not update.

 

[MinnKota]

Both of my tables have primary keys, and I am afraid that you lost me in your discussion of dynasets. Is that different than using a select query?

 

[cmmrfrds]

Is the query bound to a Form? If so, the query returns a recordset which has properties. Check under the data tab on your Form, there will be a recordset type. This controls the updateability of the recordset. Basically, after the query returns a recordset it drops out of the picture and the commincations is between the recordset and the database.

So, your primary keys are UNIQUE - i.e. don't allow duplicates?

 

[MinnKota]

I checked my Recordset Type in the Data tab of the form. I changed it to the Dynaset (Inconsistent Updates)option, and Bingo...no write conflict! Are there any downsides to this Recordset setting?

Thanks for putting my on the right path!

Christopher

 

[cmmrfrds]

Access probably determined that there could be a potential problem with the update in the joined table, but I haved used inconsistent updates in the past without a problem. Something to be aware of if you run into an inconsistency when the data is updated.

 

[MinnKota]

Turns out that the inconsistant updating isn't working. It isn't keeping the records between my two table related properly....hmmmm

 

[IKinal]

Hi,

I am working with a database that has two tables with a one-to-one relationship between id columns in each table. One of the id columns is an identity column, the other is not. I tried to enter data into both tables from a view, but when I create a new record and enter data into a field from the second table, it gives me a write conflict message. I have cascade update enabled on the relationship, but it does not seem to work. Is there a solution to this problem?

Thanks,
I. Kinal

 

[IKinal]

Sorry, I meant to start a new thread instead of replying to this one, although my problem seems similar to this problem.

I. Kinal