Write Conflict Error solutions not helping...

[Eka1618]

Hello,

I have read many posts about the Write Conflict Error that occurs in a form.
I have tried doing several of the solutions found in these forums, but
nothing is working. The following code was working for about 5 mins and now
it doesn't work anymore.

I have also tried creating an update query to try to save the record that
way, but it doesn't make this message dissapear.

Here is what I have now:


'*******************************************************************************************
'btnSend will send an e-mail to the test requestor stating that the test has
been conducte
'*******************************************************************************************
Private Sub btnSndResults_Click()
On Error GoTo btnSend_Click_Err

Dim emName As String, varItem As Variant
Dim emailBody As String
Dim emailSubject As String

'DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

Me.STATUS.SetFocus
Me.STATUS = Me.txtReview


'***************************
'SOME OTHER THINGS HAPPEN HERE'
'***************************


'send message
Me.Visible = False
DoCmd.SendObject acSendNoObject, , , emName, , , emailSubject, emailBody,
False, False
DoCmd.Close acForm, "frmQueueID", acSaveNo
Me.Dirty = False
DoCmd.OpenForm "frmMain", acNormal
DoCmd.Close acForm, "frmResults", acSaveNo

....


If I comment out the lines where I SetFocus to STATUS and assign it a new
value, then I do not get the write error, so I know for sure that the other
code is not causing the error to occur.

If anyone has any suggestions, please let me know ebcause i just do not know
how to fix this. Thank you!

~Erica~

 

[Albert D. Kallal]

A few little notes here:

Me.STATUS.SetFocus
Me.STATUS = Me.txtReview

I'm not sure why you're setting the focus to field status, you have to set
the focus to a contorl to actually modify it contents.

Anyway...

Your conflict error in as very simply the result of the following issue:

The form data is "dirty", and it simply has a pending disk write that needs
to be done. (in other words the forms data has not yet been written back to
the table).

you then go:


DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

If that above query updates the record in the table (same one your forms has
loaded). Then, when ms-access tries to write the current forms data out will
say, hey wait a second, somebody else came along and modified the record in
the table without me knowing about it (in fact it's not another user that
came along and modified this data, it was simply your query in your code
that did this). Remember the current form makes a copy of that data from
that one record into the form.

The simple solution to make sure that you don't have any pending disk rights
in your form when you go to execute any code that can potentially modified
the data that the forms record is *currently* on.

Hence, you simply need to flush (force) a disk write before you call any
code that will modify the same record

eg:

just go:

if me.dirty = true then
me.dirty = false ' force disk write of data
end if

DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

When you force/flush the forms record out to disk, then there's no pending
disk writes at that point time, and then any other code or person or even
another user (assuming a multi user system) could then update the data
without a write conflict.

 

[David W. Fenton]

A few little notes here:



I'm not sure why you're setting the focus to field status, you
have to set the focus to a contorl to actually modify it contents.

You mean:

You *DON'T* have to set the focus to a control to modify it.

You only need the focus if you want to access the .Text property
(which is what is in the control, rather than in the controlsource).

 

[Albert D. Kallal]

You mean:

You *DON'T* have to set the focus to a control to modify it.

You only need the focus if you want to access the .Text property
(which is what is in the control, rather than in the controlsource).

Yes, a mistake on my part. Thank you for clearing that up...

 

[Eka1618]

Hi Al,

Well it seems to be working for now. It is just strange that it was only an
if statement that I was missing. As for the SetFocus; I have had to teach
myself how to code in VB for Access and I used to get alot of errors because
I was using .text property, so I got in the habbit of just setting focus to
everything. As time has gone by, I've learned a lot of new ways to clean up
my code, so thanks for the tip and Thank you for solving this problem for me
because it was really annoying!

~Erica~

 

[Eka1618]

Hi again,


Well It ended up not working after a while.... I don't understand why
sometimes I will see the message and sometimes I don't. It seems that after
so long the code will not work anymore. Here is what i have now, I do not
want to use the query if I don't have to because it does is what the statemnt
me.STATUS = me.txtStatus does:


'DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

If Me.Dirty = True Then
Me.Dirty = False ' force disk write of data
End If

Me.STATUS = Me.txtReview

Maybe I do not understand what your saying about the order I should do this.
I am thinking I should save the record after I change status...

Please let me know what I can do here, thank you!

~Erica~

 

[Albert D. Kallal]

Hi again,


Well It ended up not working after a while.... I don't understand why
sometimes I will see the message and sometimes I don't. It seems that
after
so long the code will not work anymore. Here is what i have now, I do not
want to use the query if I don't have to because it does is what the
statemnt
me.STATUS = me.txtStatus does:


'DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

If Me.Dirty = True Then
Me.Dirty = False ' force disk write of data
End If

In the above, you commented out the code that does the OpenQuery.
(is this by desing, or a mistake for your posting???)

Regardless, don't just try and thow code at the wall, and hope it sticks.

Stop, and think about what is going on here.

You want to FORCE a disk write of *pending* data in the form BEFORE
***any*** code is run that can update the underlying forms reocrd.

Hence, you would go:


If Me.Dirty = True Then
Me.Dirty = False ' force disk write of data
End If

DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

Now, the above is theoretical, because the doCmd.Openquery in your posted
code is not actually being run. (again, was that a mistake, and why post
code that is commented out then? )

Is there any other code being run in other forms that can modify the same
record that this form is currently on?

 

[Eka1618]

Al,

Yes, there is code that is doing the same thing as the query:

Me.STATUS = Me.txtReview <--THIS STATEMENT CHANGES THE RECORD

I have the update query in my example because I tried to use it instead of
the statement above, but they both do the same exact thing and both cause the
write conflict error. I would prefer not to use the query if it is not needed
in this situation, but I have it listed in this thread incase someone thinks
I should go ahead and use it.


I understand the order in which the the code should be written. What I do
not understand is why sometimes it works, and sometimes it doesn't. There are
some other things going on in this event, Here is the entire btnSend_Click()
event:


Private Sub btnSndResults_Click()
On Error GoTo btnSend_Click_Err

Dim emName As String, varItem As Variant
Dim emailBody As String
Dim emailSubject As String

'DoCmd.OpenQuery "qryUpdateQueueStatus", acViewNormal

If Me.Dirty = True Then
Me.Dirty = False ' force disk write of data
End If

Me.STATUS = Me.txtReview

emailSubject = "Test Queue ID has been Conducted"

On Error GoTo btnSend_Click_error
If Me.lboRequestor.ItemsSelected.Count = 0 Then
MsgBox "Please select a test requestor"
Exit Sub
End If

Me.txtRequest.SetFocus

emailBody = "Hello," & vbCrLf & vbCrLf & _
"The product test request for Request Number: " & txtRequest.Text

Me.txtQID.SetFocus

emailBody = emailBody + " has had a test conducted for Test Queue: " & QID &
"." & vbCrLf & vbCrLf & _
"To review, Please log into the Product Engineering Database." & vbCrLf &
vbCrLf & _
"Thank You!"

On Error GoTo btnSend_Click_error
If Me.lboRequestor.ItemsSelected.Count = 0 Then
MsgBox "Please select a test requestee"
Exit Sub
End If

For Each varItem In Me.lboRequestor.ItemsSelected
emName = emName & Chr(34) & lboRequestor.Column(2, varItem) & Chr(34) & ","
Next varItem

'remove the extra comma at the end
'add the requestor to the e-mail list recipients
emName = Left$(emName, Len(emName) - 1)

'send message
Me.Visible = False
DoCmd.SendObject acSendNoObject, , , emName, , , emailSubject, emailBody,
False, False
DoCmd.Close acForm, "frmQueueID", acSaveNo
DoCmd.OpenForm "frmMain", acNormal
DoCmd.Close acForm, "frmResults", acSaveNo

btnSend_Click_error:
If Err.Number = 2501 Then
MsgBox "You just canceled the e-mail", vbCritical, "Alert"
End If

btnSend_Click_Exit:
Exit Sub

btnSend_Click_Err:
MsgBox Error$
Resume btnSend_Click_Exit

End Sub




Let me know what you think, Thhank You!

~Erica~

 

[Eka1618]

Al,

I dont understand because if I change the code to what your example says and
run the form, it will work for me. But if I close the DB, reopen it and try
run the same form again, the write error comes back, but the code is still
there...

~Erica~