Suppress the Write Conflict Dialog Box

[EarlCPhillips]

I have a form producing a datasheet-view of multiple rows showing "today's
schedule." I have written SQL code to update selected fields within a row
(such as how many people showed up and the times they arrived and departed),
and associated data that is not displayed (such as total hours worked), as
the user changes the values. I also allow the user to change more than one
row.

When the user tries to change the second or another row, the Write Conflict
dialog box appears telling the user that "this record has been changed by
another user since you started..." I wish to suppress that dialog box since
each update to a field that the VBA code changes contains range checks and
other edits and the code often updates other related-but-non-displayed
fields. I do not want the user to have the choice to do anything but accept
what the VBA code changes. I do not want them to choose "Save Record" as
that wipes out the changes I have made to related fields.

Is there a way to suppress the "Write Conflict" dialog box? If not, what
Event will allow me to display an instruction message before the "Write
Conflict" dialog box is displayed?

 

[Albert D. Kallal]

Actually, you request to surpress the dialog box is the wrong anser.

that dialog box is comming up because you are modying a reocrd, but that
reocrd has PENDING writes wating.

In other words, that "other" user is not actually anhter user, but in fact
your code!!!

In fact, the term "dirty" is used to define a reocrd that been modifed, but
not yet written to disk (actualy, the term "dirty" was also used often in
the mainframe days also!!).

Is there a way to suppress the "Write Conflict" dialog box? If not, what
Event will allow me to display an instruction message before the "Write
Conflict" dialog box is displayed?

Here is what you need to do:

BEFIRE you start you modifying code, you need to flush that reocrd to disk
(likey the current record we are on). So, in your code go:


if me.Dirty = true then
me.dirty = false ' force a disk write
end if

You sql + udpate code follows:

So, if you force the disk write just before you run your code, you wll NOT
get that conflict.

in fact, if a user is editng a customer reocrd, and I have a buttion to
launch customer details, then I will OFTEN force a disk write on the custoer
form BEFORE I launch the form with child reocrds. This ensures that the
customer record is writene to disk, and futher ensures that any reprot, or
code that runs can use sql to fetch the vlaues. If hte form's data has not
been commited to disk...it is dirty..and as you found out..any other code
that runs will state that "hey" this reocrd has pending disk writes...

So, in code you see me go:

if me.Dirty = true then
me.Dirty = false
end if

docmd.Openform "frmDetails",,,"customer_id = " & me!id

 

[Albert D. Kallal]

Sorry, bumped the send key...

Here it is again with spell check!!!

Actually, you request to suppress the dialog box is the wrong answer.

that dialog box is coming up because you are modifying a record, but that
record has PENDING writes waiting.

In other words, that "other" user is not actually another user, but in fact
your code!!!

In fact, the term "dirty" is used to define a record that been modified, but
not yet written to disk (actually, the term "dirty" was also used often in
the mainframe days also!!).

Is there a way to suppress the "Write Conflict" dialog box? If not, what
Event will allow me to display an instruction message before the "Write
Conflict" dialog box is displayed?

Here is what you need to do:

BEFORE you start you modifying code, you need to flush that record to disk
(likely the current record we are on). So, in your code go:


if me.Dirty = true then
me.dirty = false ' force a disk write
end if

You sql + update code follows:

So, if you force the disk write just before you run your code, you will NOT
get that conflict.

in fact, if a user is editing a customer record, and I have a button to
launch customer details, then I will OFTEN force a disk write on the
customer
form BEFORE I launch the form with child records. This ensures that the
customer record is written to disk, and further ensures that any report, or
code that runs can use sql to fetch the values. If the form's data has not
been committed to disk...it is dirty..and as you found out..any other code
that runs will state that "hey" this record has pending disk writes...

So, in code you see me go:

if me.Dirty = true then
me.Dirty = false
end if

docmd.Openform "frmDetails",,,"customer_id = " & me!id


The above would open a frmDetails with the related records by use the
"where" clause to restrict customer_id to the current records id in the
form.

 

[EarlCPhillips]

The Write Conflict dialog box has now disappeared, but a Microsoft Office
Access dialog box has popped up stating "The macro or function set to the
BeforeUpdate or ValidationRule property for this field is preventing
Volunteer Database from saving the data in the field."

The Properties ValidationRule is empty, so it has to be the code in the
BeforeUpdate Event Procedure for the Volunteer Database that is prompting the
message, but I cannot figure out why. The code takes the "final number" of
volunteeers entered, computes the hours worked by finding the difference
between arrival time and departure time entered, multiplies the final number
times that value and updates "final number" and "hours worked" to the row
using SQL code of UPDATE tblActivity Set FinalNumber = value, FinalHours =
value, etc.

Any help would be appreciated.

Earl C Phillips
Ex-mainframer Learning Access To Help
Improve Efficiency at Kansas City Food Bank

 

[EarlCPhillips]

The solution was to take out all SQL code that updated anything from the VBA
code. I still kept all the SQL reads used to verify appropriateness of data
(such as days closed, time-of-day conflicts, too many volunteers on a shift)
and all intra-field edits, etc. Then Access updated everything on its own
without conflict, even if I moved from one record to another.

Earl C Phillips