StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWrite Conflicts - even with me.dirty
text
Body
copied!<p>In an application that I am writing, I am getting Write Conflicts when I use VBA code to change the value of a checkbox on a form. I have tried inserting the following code into the events that have VBA changing the values of any object on the form:</p> <pre><code>If Me.Dirty Then Me.Dirty = False End If </code></pre> <p>But the problem persists - as if the record is not being saved before I look to change anything through manual manipulation.</p> <p>Here is some examples of the full code from the form where I am encountering this problem:</p> <p><i>Code for when Blank Pricing checkbox is changed:</i></p> <pre><code>Private Sub chkSupAllowBlankPrice_AfterUpdate() If Me.Dirty Then Me.Dirty = False End If If (chkSupAllowBlankPrice.Value = True) Then chkSupRequirePrice.Value = False End If End Sub </code></pre> <p><i>Code for when Require Pricing checkbox is changed:</i></p> <pre><code>Private Sub chkSupRequirePrice_AfterUpdate() If Me.Dirty Then Me.Dirty = False End If If (chkSupRequirePrice.Value = True) Then chkSupAllowBlankPrice.Value = False chkSupAllowBlankPrice.Visible = False chkSupAllowBlankPrice.Enabled = False chkSupAllowBlankPrice.Locked = True lblSupAllowBlankPrice.Visible = False Else chkSupAllowBlankPrice.Visible = True chkSupAllowBlankPrice.Enabled = True chkSupAllowBlankPrice.Locked = False lblSupAllowBlankPrice.Visible = True End If End Sub </code></pre> <p>Not sure if it helps, but the tables are stored in a SQL Server express database - hence the tag.</p> <p>-- Edited 05/29/2009 @ 1201 hours --</p> <p>I have tried commenting out all object value changes, leaving in only visible, locked, and enabled changes - but I keep getting write conflicts. I tried putting the Me.Dirty = False at the end of the event procedure, and I even tried to remove it. So far, I am getting write conflicts whenever I change Require Pricing or Allow Blank Pricing, without the other value being changed by VBA code.</p> <p>-- Edited 05/29/2009 @ 1318 hours --</p> <p>The fields that these checkboxes manipulate -will not- accept any changes once the record is created, generating a Write Conflict any time I attempt to work with them. Now I am totally confused, thinking of gutting everything and starting over.</p> <p>-- Edited 06/01/2009 @ 1209 hours --</p> <p>After investigation, it seems there is a number of check constraints defined on the server for the tables involved which I cannot remove. Something is causing the linked tables to always report dirty even when items have not been changed - and I think Access and SQL are fighting it out with default values. I am going to wipe all tables, delete all information, and start over with my design, as the check constraints don't appear to be removable without dropping the table anyway. Thank you everyone for the help, could this question be closed - referring the problem to this new question (<a href="https://stackoverflow.com/questions/936735/updates-to-records-not-allowed-write-conflict">Updates to Records not allowed - Write Conflict</a>)?</p> <p>-- Edited 06/03/2009 @ 1307 hours --</p> <p>Cross-posted from other question, solution described below for all that were curious. Thank you to all the people who banged their heads with me to get this far, I really aprpeciate the help. I found out that a strange problem that crops up when using Yes/No checkboxes and SQL Server with Access. Apparently, Access will interpret NULL as No - changing the value, but SQL Server will not interpret NULL as a No in a Bit field (what Yes/No gets turned into in conversion) so it throws a Write Conflict error when a value is not required, and is NULL. The solution was to redesign the table so that a value was required, and that there was a default value assigned for EVERY former Yes/No checkbox. This solved the mysterious Write Conflict messages, and allowed changes to records once they were created.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload