Error message unrelated to what's going on 1

[huggybear]

Hi,

I'm having a problem with an error message stating that I have a .cancelupdate without an .addnew or .edit. First of all, that's not true and second, it crops up at strange times.

I know you will want code, but believe me, you don't want to look at all the code I'd have to send to show you, so I'll try to give you the gist of it.

I have a form, frmProj for short, that is the main form of the application. With a brand new, empty database, a user can create a project. That's fine. The user can then add a Sponsor by choosing from a combo if there's any Resources available, or press a button to open a pop up dialog, AddResource to add Resources, if there are none or the one user wants is not there. This pop up does triple duty as there are a couple other places it can be called from.

For Sponsor, it takes the first and last names and adds them to the Resource table, and then assigns that resource to the Project as Sponsor. It is here that recordsets are created, opened, .addnew-ed and .updated. Nowhere in this area is there any .cancelupdate at all.

User is then returned to frmProj where they can now choose to assign a Manager to the project. The Process is identical except that now the resource is assigned to the Manager field. User is returned to frmProj.

Now this is where it gets dicey. When code execution returns to frmProjs module, frmProj is requeried to reflect the changes. It is at this point that I sometimes get the message about a .cancelupdate. I can't understand that. And, worse, the problem seems to cascade into other procedures if I trap it at it's original location. What I mean is that every time I try to requery the form, I get the .cancelupdate error again.

What can cause such baffling behaviour?

Also, I am getting write conflict messages at times when I try to add a Manager right after adding a Sponsor. This only occurs when the AddResource pop up is called, not when a resource is chosen from the combo. I have tried various solutions offered in previous posts, such as calling RunCommand accmdSaveRecord, but that doesn't seem to help.

I have an idea these problems are related, but I can't put my finger on the solution.

Thanks for any help.

Bear

 

[thornmastr]

If you are getting a cancel update message, you, or something within your system has issued an update request. Remember, this can occur because of a physically issued update, such as

Me.dirty = false

Or by the focus changing from the current record to another either by navigating to another record, moving from a subform to a main form, jumping between windows, but I think you are getting the idea.

You might, in a global module, declare two global values,

Public gform as string and

Public gfunc as name.

On the open event of every form set gform to me.name.

On entering every functional procedure or subroutine, set gfunc to the name of that procedure. Every function and subroutine should contain an on error go to statement that should either inform the user of both the error number and error description so that he can report it to you, or log both those items along with the form and function which generated the error.

This should certainly get you started finding the problem.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[huggybear]

Thank you Robert,

I have found the sequence of events that will trigger the errors. This time I am including some code that may help to isolate the cause.

When user wants to designate either a Sponsor or a Manager, they have two choices. Either choose from a combo, or if it's a new Resource, by clicking a button that opens a pop up dialog with text boxes for entry of first and last names. The code in the dialog that does the work is this:

Private Sub cmdOK_Click()
On Error GoTo Errhandler

Dim lngProjectKey As Long
lngProjectKey = Forms!frmProjectInformation![ProjectKey]

If txtFirstName <> &quot;&quot; And Not IsNull(txtFirstName) _
And txtLastName <> &quot;&quot; And Not IsNull(txtLastName) Then

If Not CheckForDuplicates(txtLastName, txtFirstName) Then

Dim lngFK As Long
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(&quot;tblResource&quot
With rs
.AddNew
!ResourceName = txtLastName
!FirstName = txtFirstName
lngFK = !ResourceKey
.Update
.Close
End With

Select Case Me.OpenArgs

Case &quot;AddPM&quot;

Set rs = CurrentDb.OpenRecordset(&quot;tblProject&quot
With rs
.MoveFirst
Do While Not .EOF
If !ProjectKey = lngProjectKey Then
.Edit
![ManagerFK] = lngFK
![ReviseDate] = Date
.Update
.Close
Exit Do
End If
.MoveNext
Loop
End With
RunCommand acCmdSaveRecord
RunCommand acCmdClose

Case &quot;AddSponsor&quot;
Set rs = CurrentDb.OpenRecordset(&quot;tblProject&quot
With rs
.MoveFirst
Do While Not .EOF
If !ProjectKey = lngProjectKey Then
.Edit
![SponsorFK] = lngFK
![ReviseDate] = Date
.Update
.Close
Exit Do
End If
.MoveNext
Loop
End With
RunCommand acCmdSaveRecord
RunCommand acCmdClose

End Select

If Not rs Is Nothing Then
Set rs = Nothing
End If

Else

MsgBox &quot;That name is already in the list. One way to differentiate between people with &quot; & vbCrLf _
& &quot;similar names is to use a middle initial in the first name box.&quot;

End If

Else
MsgBox &quot;One or more of the required fields has no data.&quot; & vbCrLf _
& &quot;If you wish to continue, be sure all the required fields contain data.&quot;
txtLastName.SetFocus

End If

ExitSub:
Exit Sub

Errhandler:

MsgBox Err.Description
Resume ExitSub

End Sub

That gets the new resource into the resource list and also assigns that resource to the appropriate role.

Control is then returned to the main form in this routine (and an almost identical one for Manager):

Private Sub cmdAddSponsor_Click()
On Error GoTo Errhandler

Dim lngProjectKey As Long
Dim rs As Recordset
lngProjectKey = Me!ProjectKey

DoCmd.OpenForm &quot;fpupNewResource&quot;, acNormal, , , , acDialog, &quot;AddSponsor&quot;

On Error Resume Next
RunCommand acCmdSaveRecord
Err.Clear
On Error GoTo 0
On Error GoTo Errhandler
Echo False
' Me.Requery
Set rs = Me.RecordsetClone
With rs
If .RecordCount Then
.FindFirst &quot;[ProjectKey] = &quot; & lngProjectKey
If .NoMatch = False Then
Me.Bookmark = .Bookmark
End If
End If
.Close
End With
Echo True
cboSponsor.Requery
cboManager.Requery

ExitHere:
Exit Sub

Errhandler:
MsgBox Err.Description
Err.Clear
Resume ExitHere

End Sub


So here's what happens. After control is returned to the main form, the error gets triggered at either of two lines in the above routine: either at the RunCommand line or at the Me.Requery line. The error is #7787, the Write Conflict error. Once that error is triggered, the .Update errors start.

Now the odd thing is, these errors only occur when user enters a Manager from the combo, then enters a Sponsor through the dialog. So far, I can't find another sequence of events that will trigger the errors. The code for Sponsor and Manager is identical except for the difference in the field that is edited.

Any ideas?

If you are still reading this, many, many thanks!

Bear

 

[thornmastr]

Sorry it took me a while to get back with you. The driving concept behind this sucker is pretty slick. It is also not that easy to debug. If a write operation is pending, a requery will certainly trigger it. You may have a conflict , or a point of Access conflict simply because you have changed something on a record by a form alteration and then you begin manipulating the recordset clone. I would change your run command that does your update to the following code

If me.dirty then
Me.dirty = false ‘force update
Endif

Also, if you are not using a form before update event create one simply to be able to hang a break point. You know that immediately after that function exits your update will happen.
I would concentrate on making sure your update triggers after you are finished with the recordset clone. Access does not like dealing with a cloned object expecting an update. You may want to do your update before you clone out.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[huggybear]

Robert,

Thanks so much for your help. I will try to get this to work without causing errors because I'd prefer to have my code working without ignoring errors. That is what I'm doing now, by trapping the error and using acDataErrContinue to ignore it. It's working, but I get the feeling it could find a way to crash in a heartbeat.

I haven't used the dirty property before so I guess I'll need to read up on it a bit.

Again, thanks, and, in my estimation, you deserve a gold star for wading through that patch of code and deciphering it!

Bear