Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Avoiding Write Conflicts

Discussion in 'Information Technology' started by weegee@metronet.co.uk, Jul 28, 2006.

  1. weegee@metronet.co.uk

    weegee@metronet.co.uk
    Expand Collapse
    Guest

    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?
     
  2. Loading...


  3. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    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@metronet.co.uk" wrote:

    > 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?
    >
    >
     
  4. weegee@metronet.co.uk

    weegee@metronet.co.uk
    Expand Collapse
    Guest

    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?

    Barry Gilbert wrote:

    > 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@metronet.co.uk" wrote:
    >
    > > 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?
    > >
    > >
     
  5. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    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

    "weegee@metronet.co.uk" wrote:

    > 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?
    >
    > Barry Gilbert wrote:
    >
    > > 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@metronet.co.uk" wrote:
    > >
    > > > 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?
    > > >
    > > >

    >
    >
     
  6. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    Barry Gilbert <BarryGilbert@discussions.microsoft.com> wrote:

    >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
     
  7. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    Guest

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

    Barry

    "Tony Toews" wrote:

    > Barry Gilbert <BarryGilbert@discussions.microsoft.com> wrote:
    >
    > >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
    >
     
  8. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    Barry Gilbert <BarryGilbert@discussions.microsoft.com> wrote:

    >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
     

Share This Page