Avoiding Write Conflicts

[weegee]

I have a form which uses a query comprised of SQL Server tables as its
recordsource.

I want to a routine so that when the user clicks on a check box called
'PaymentUnderflow'
for a Company, that all instances of that company within the recordset
get set to -1 as
well at the same time

Im attempting to do this through code, however I get a 'Write
Conflicts' message box
as it sees that a code routine is trying to update the same set of
records at the same
time, as the user is clicking PaymentUnderflow

For example the form is showing (In continuous forms format) the
following records

Company Name Payment reminder PaymentUnderflow
============ ============== ===============

Ziadal Trading April 2006 False
Ziadal Trading July 2006 False
Ziadal Trading August 2006 False

Now I want to update all occurances of Ziadal in one hit

How can i do this in code please?

 

[Guest]

My first question would be: why are you storing totals values? It's
considered a best practice to derive these dynamically when you need them.
The rare exceptions are when you are denormalizing for performance reasons.
Otherwise, it's better to not store calculated values.

Barry

 

[weegee]

Hi there

Im not storing totals values?

They are inddivual payment records for these company...just that I
havent
shown the record id

Any other ideas?

 

[Guest]

The pitfalls of multi-tasking. Sorry.

In the after update event of the checkbox, call a query the updates the
PaymentUnderflow column for that company.
In the update query, the "Update To" value for the PaymentOverflow would
point to the checkbox in the detail section of your form. In the criteria row
for the CompanyName field in the query, have it point to the CompanyName
textbox in the detail section.

To call the query from code use:
DoCmd.SetWarnings False ' turns off the nag message
DoCmd.OpenQuery qryPaymentOverflowUpdate" 'or whatever you name the query.
DoCmd.SetWarnings True 'turns the nag back on

Then requery the form.

Hope this helps,
Barry

 

[Tony Toews]

To call the query from code use:
DoCmd.SetWarnings False ' turns off the nag message
DoCmd.OpenQuery qryPaymentOverflowUpdate" 'or whatever you name the query.
DoCmd.SetWarnings True 'turns the nag back on

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

I suggested OpenQuery rather than RunSQl, but I agree on all points anyway. I
was trying to keep it simple.

Barry

 

[Tony Toews]

I suggested OpenQuery rather than RunSQl, but I agree on all points anyway. I
was trying to keep it simple.

Ah, I just saw the SetWarnings, everything else looked the same and
threw in my standard blurb.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm