Error Message After Delete

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have several pairs of synchronized subforms in an application. I have a Delete
button for each pair that uses the following code or similar to delete a record
in the second subform:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If
ExitHere:
Me!SubName.SetFocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontractorDivisionList!DivisionName & vbCrLf _
& "Can Not Be Deleted From The Database Because There Are" & vbCrLf _
& "One Or More Locations Recorded For The Division.", , "Division Can Not
Be Deleted"
Else
MsgBox Err.Description, , "Error # " & Err.Number
End If
Resume ExitHere

When I try to delete a record that has related records, I get the message for
Err 3200 and all seems to work fine. However, if I then try to move to a
different record in the first subform, I get the error message "Operation Not
Supported In Transactions". There is code in the OnCurrent event of the first
subform:

Me.Parent!SubcontractorDivIDNum = Me!SubcontractorDivID

that synchronozes the subforms and it works error free when navigating through
records in the first subform until I try to delete a record with related records
in the second subform.

Does anyone have any ideas about what is causing the error message?

Thanks!

Steve
 
Steve, I dont know that I have enough information from you but heres some
thoughts.
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If
Click to expand...
I notice on the 4th line of code you have an End If but I didnt see an If,
so if you are able to, I suggest you post the code above the snipet you
already posted (above ->DoCmd.SetWarnings False...).


Also, just for a quick test: After deleting a record on the sub-form,
before you attempt to move to another record on the main form, click on a
field of the main form (make sure the main form has the focus), then attempt
to move to the next record on the main form.

Let us know

Mike Krous
 
Mike,

Thank you for responding!

First, Here is a more complete sample of the code:

If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Me!SFrmSubcontractorDivisionList.SetFocus
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If
ExitHere:
Me!SubName.SetFocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontractorDivisionList!DivisionName & vbCrLf _
& "Can Not Be Deleted From The Database Because There Are" & vbCrLf _
& "One Or More Locations Recorded For The Division.", , "Division Can Not
Be Deleted"
Else
MsgBox Err.Description, , "Error # " & Err.Number
End If
Resume ExitHere

When I posted I described the problem as occuring when moving to a new record in
the first subform not the main form. However, I did try your quick test and
found that the problem occurs when I try to move to a new record in the main
form, first subform or second subform. This seems to say that Access tried to
execute this line of code,DoCmd.RunCommand acCmdDeleteRecord, an error occured,
the error handler executed and Access hung up on the DoCmd.RunCommand
acCmdDeleteRecord code???

I did try exactly as you said for a quick tes, I clicked on a field in the main
form and then tried to move to a new record. The same error message appeared.

Steve
 
Mike,

Additional information ----

I tried commenting out the On Error GoTo ErrorHandler statement. I got Access'
standard 3200 error message when I tried to delete. I clicked on End in the
error message. I the tried to go to a new record in the main form and the first
subform and got the same error message as previously in both cases.

Steve
 
Your right you did say in the sub-form, sorry for that! However I do believe
I am a little unclear at the moment, how many subforms do you have on this
form and are they subforms of subforms? I was originally thinking you had
one form and one subform...just curious?

also im not sure, but you could try a DoCmd.Requery immediatly after the
DoCmd.RunCommand acCmdDeleteRecord. The idea here is to maybe allow access
to refresh itself before you attempt to move. Although I must say FWIW, I
did a search on google and no-one showed a requery in there examples, but
they also didnt have subforms in the examples either...

HTH

Mike Krous
 
Mike,

Added DoCmd.Requery as you suggested - still get the error. I don't think it
ever gets to the Requery. It gets to DoCmd.RunCommand acCmdDeleteRecord, an
error occurs and execution jumps to the error handler.

There is a main form with a tab control. On one page of the tab control is a
subform(first subform) of the main form and another subform(second subform) of
the first subform. The second subform is external of the first subform and is
synchronized with the first subform through a textbox.

Steve
 
ok sorry for the previous hack attempts. Lets take a more logical and
structured look at this:

first off I took your message and did a search on Microsoft's Knowledgebase
and found this article which is similiar but not exact to our problem,
however it does give us some insight as to the solution:
http://tinyurl.com/uzoh
(for the archives:
http://support.microsoft.com/default.aspx?scid=kb;en-us;295229&Product=acc
)

so next I did some testing and here is what I think you need.

at the top of your delete routine you need to goto error handler line:
On Error GoTo ErrorHandler

next I placed this code in your error handling routine:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontractorDivisionList!DivisionName & vbCrLf _
& "Can Not Be Deleted From The Database Because There Are" & vbCrLf _
& "One Or More Locations Recorded For The Division.", , "Division Can
Click to expand...
Not Be Deleted"
'** NEW CODE **
lngID = txtid 'get a unique value from the current record, (you'd
have to create this variable)
Me.Requery 'requery the data to fix our broken link
txtid.SetFocus 'now set focus to the unique control for faster
searching
'perform search to return us to the record we were on before we issued
a requery
DoCmd.FindRecord lngID, acEntire, , acSearchAll, True, acCurrent, True
'** End New Code **
Else
Click to expand...
<snip>

The new code will have to be adapted to your controls but I think you get
the idea. What I think is happening is Access has a bug and looses the
link between subforms, if we dont issue the requery we cant fix our broken
link.

HTH

Mike Krous
 
Mike,

That eliminated the "Operation Not Supported In Transactions" error message.
Thank you very much for the help!!

The only thing now is FindRecord. When Requery is executed the recordset jumps
to the first record as expected and then the FindRecord code DOES NOT bring the
recordset back to the record it was at when the Delete was executed. Is
FindRecord suppose to move you to a specified record? Rst.Findfirst finds the
bookmark of a specified record but then it takes additional code to move the
recordset to that record. Does FindRecord need similar additional code?

I appreciate your help!

Steve

PS - I also learned about TinyURL.com from you. That will come in handy!
 
Steve, good to hear your almost there. as for the find code, that was just
something I placed in there to show you one example of restoring your
location after the requery. There are a few ways actually to return to your
location after a requery, each is subject to how you have designed your
form. For example, the form I created to test your main subform (the one
with the delete button) was only a simple form with two textbox controls, so
a simple DoCmd.FindRecord works just fine. I am assuming you have a more
intricate setup and may require different methods. However the same
principals apply to 'returning to your location' after a requery.

1. Mark/Store the current record. (usually done via a unique ID field, but
not the only way)
2. Issue the requery
3. Issue a find to return. (find could mean any method that works in your
situation) to return to the Marked/Stored record.)

If we look at the code I wrote:
lngID = txtid '-> Step 1. Storing the unique id.
Me.Requery '-> Step 2. Issue the requery
txtid.SetFocus '-> Step 3. the next command searches faster
'if we are in the field were're searching, so this
'is simply part of step 3.

'-> Step 3. this is the actual action in step 3. with this command I am
'looking for the unique id I stored earlier, I am searching the entire
'recordset and I am looking in the current field only (faster).
DoCmd.FindRecord lngID, acEntire, , acSearchAll, True, acCurrent, True



Each of these steps are generalizations and need you to look at your
specific environment and see what works for you. FWIW If you need more
assistance on returning to the record, I would post a new topic.

HTH

Mike Krous
 
Back
Top