write conflict

[Lez]

Hi Guys,

I have an quote form with a sub form for the line items.

I have set the pk correctly as quoteID

The problem I have is when I try to add a new quote or save the current one
I get an error message 'Write Conflict'

"This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make changes."

I can then either copy to clipboard or drop changes and not given the chance
to save the record.

I have some derived fields to get line values for the main form summary.

I am using access 2007 front end to a SQL back end, the only required field
value is the quoteID.

Any suggestions would be appreciated.

Lez

 

[Sylvain Lafontaine]

A good suggestion would be to provide more details about the type of
frontend that you are using (MDB or ACCDB database file with ODBC Linked
Tables or an ADP project?) and the structures of the tables and of the
queries used as the record sources for the form and the subform.

Also, the use of some datatypes is incompatible or can lead to some rounding
error; hence the error returned because Access verify the value of each
field before making the update and they must match exactly. You should take
a look with the SQL-Server Profiler in order to see what's going on.

You can try adding a timestamp field to your tables; this will quite
possibly solves your current problem but can also leads you to other
problems later.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Lez]

Hi Sylvian,

Many thanks for that. my database FE is ACCDB with ODBC connection and I
think I have identified the problem due to my main form picking up values
from the sub form and adding additional values in the main form, let me
explain:

My subform is a line item invoice form, users select the items, this sub
form has hidden fields for the netttotal, vattotal and grosstotal.

The main form has hidden fields that refences these values
[txt_quoteLI_subtotal] =[frmQuotelineitems].[Form]![txt_vatTotal] etc.

My main form also has a field for carriage cost to be added to the order
value, so I have fields that gets the values from the main form hidden
fields and we need to add the carriage cost to these values that have been
grabbed from the sub form, so assume that is were the error lies

[txt_quote_subtotal]=(Nz([txt_quoteLI_subtotal]+[txt_carraiage],0))

My DS_quote has a datetime field, but do not have this in
DS_quote_lineitems. I do not understand how to add a timestamp to check for
an table update, can you advise please?

Oddly though, I have a field to add notes to the main form, which does not
use any data from the sumform, and if I try add any value into it the same
error occurs.

Also I see the SQL profiler, but do not understand how to read what I am
seeing, can you advise on this please.

Kindest regards
Lez

 

[Sylvain Lafontaine]

Hi, sorry to say that but I don't really know much about ODBC Linked tables;
you should repost in a more appropriate newsgroups such as
microsoft.public.access.odbcclientsvr or m.p.a.externaldata where you're
more likely to find knowledgeable people about ODBC Linked tables.

In your case, understanding how Access is doing its optimistic locking
strategy for updating the data is the key to understanding your problem and
using the SQL-Server Profiler will greatly help you for that; so you should
take a deep look at the Profiler in order to understand what it's showing
you (everything to understand your problem and see what's going on between
Access and SQL-Server is there).

Also, you should always go toward simplifying your problem when trying to
resolve it; so you see what happens when your remove these updating from the
subform to the main form. The fact that you have a problem with your notes
on the main form might be very important in this regard. What happens if
you add a simple integer field to your main form and try to update it?

Finally, you still don't provide the structure of your tables and of your
queries and there are other importang missing fact. For example, we don't
know if the subform is a single form or a continuous form and if the fields
used to update the main form are linked fields to the backend table(s) or
not.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi Sylvian,

Many thanks for that. my database FE is ACCDB with ODBC connection and I
think I have identified the problem due to my main form picking up values
from the sub form and adding additional values in the main form, let me
explain:

My subform is a line item invoice form, users select the items, this sub
form has hidden fields for the netttotal, vattotal and grosstotal.

The main form has hidden fields that refences these values
[txt_quoteLI_subtotal] =[frmQuotelineitems].[Form]![txt_vatTotal] etc.

My main form also has a field for carriage cost to be added to the order
value, so I have fields that gets the values from the main form hidden
fields and we need to add the carriage cost to these values that have been
grabbed from the sub form, so assume that is were the error lies

[txt_quote_subtotal]=(Nz([txt_quoteLI_subtotal]+[txt_carraiage],0))

My DS_quote has a datetime field, but do not have this in
DS_quote_lineitems. I do not understand how to add a timestamp to check
for an table update, can you advise please?

Oddly though, I have a field to add notes to the main form, which does not
use any data from the sumform, and if I try add any value into it the same
error occurs.

Also I see the SQL profiler, but do not understand how to read what I am
seeing, can you advise on this please.

Kindest regards
Lez

A good suggestion would be to provide more details about the type of
frontend that you are using (MDB or ACCDB database file with ODBC Linked
Tables or an ADP project?) and the structures of the tables and of the
queries used as the record sources for the form and the subform.

Also, the use of some datatypes is incompatible or can lead to some
rounding error; hence the error returned because Access verify the value
of each field before making the update and they must match exactly. You
should take a look with the SQL-Server Profiler in order to see what's
going on.

You can try adding a timestamp field to your tables; this will quite
possibly solves your current problem but can also leads you to other
problems later.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[???]

????????