Write Conflict with Subform

[el zorro]

I have a subform that the user accesses to input data. The user then selects
a record from the subform, clicks a control button, and a reference to the
selected record is recorded in a table on the main form. All of this actually
works, but when leaving the subform, the user the user is prompted with the
'Write Conflict" dialog box.

Access apparently thinks a second user has modified the record because, the
message starts with "This record has been changed by another user since you
started editing it." There are no other users right now, beacuse the databae
is in the design/testing phase while I work the bugs out in converting from
the .mdb versin to adp/SQL/.

AS it stands, I can instruct the users to simply click the "Save" button on
the Write Conflict dialog box. But that's not optimal. If I could have
"save" selected automatically, or skip the warning all together, that would
be better.

As a test, I have manually executed Save Record before leaving the subform,
but that didn't work. I have tried using a save on the On Deactivate event of
the subform, but no dice there either. (There was a previous post on this
subject, but the thead concluded without a solution.)

Any ideas? Thanks!

 

[Sylvain Lafontaine]

Take a look with the SQL-Server Profiler and quite probably that you'll see
what's happening here; ie., you'll see that Access is trying to write
something using the older values as verification for its optimistic locking.

Also, your mention of "The user then selects a record from the subform,
clicks a control button, and a reference to the selected record is recorded
in a table on the main form." looks suspicious to me. Even when you have a
1-1 relationship, you should always use the principle that the table under
the subform will store a reference to the table of the main form and never
the inverse.

 

[el zorro]

Hi Sylvain-- I poked around in Enterprise Manager to for a Profiler-- but
didn't see anything. But anyway-- that's getting into the tall weeds for me.
I let our Tech Services people fool with the Server, and mostly limit my work
to programming the application.

Regarding your second point, I have always felt that I was using the subform
in a nonconventional way, but it has been working well in .mdb and still
works in .adp, but with the new need for the user to click an additional
button ("save" in the Write Conflict box). The subform is a log of activity
associated with the master record, so all entries in the (child) subform
belong to the master (parent) record. The user needs to be able see a listing
of all of the entries associated with the parent record, and then to enter
one as being "active." This selection is made by selecting the appropriate
subform entry, and hitting a command button that assigns the unique record
number in the subform to a field in the Master form.

I can leave things as they are, but if there was some way to tell Access not
to worry about the alleged Write Conflict, that would save my users a mouse
click.

 

[Sylvain Lafontaine]

In EM, looks under Tools, right below "Query Analyser"; you should find
something about Profils or Profiler. Don't expect to solve your problem
without a clear understanding of what's going on here.

 

[Rob]

Sylvain,

I am having a similar problem with my database. I have an Access front end
and SQL back end. I have a main form that creates a record with some data
and a subform that allows the user the create multiple records in another
table that coinsides with the main form data. I have a button that allows
the user to add a new record to the subform and when the button is clicked it
takes the primary key data from a field in the main form and copies it to the
same field in the subform to create the link. In SQL I made sure that the
subform had an int field that was incremented to keep from creating a
duplicate (this allows the user to create mutliple records on the subform
that link to the one record on the main form).

I have not created any relationships in Access since I tell the subform to
take the main forms' key field data to create the link. I am getting two
errors:

Invalid Use of Null
Write Conflict

There is no one else in the database since I am testing it right now. I can
go back to SQL and delete or edit the record in question. Just not through
Access. I am not sure what to do since there is not invalid use of null - I
have determined that the key field data is being copied. The record will
save if I tell the write conflict to drop changes and the key field data is
there from the main forms' data.

Any suggestions would be greatly appreciated.