By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Error Message After Delete

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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 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


"Steve" <sa****@penn.com> wrote in message
news:cd******************@newsread1.news.atl.earth link.net... 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

Nov 12 '05 #2

P: n/a
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 Krous" <m.krous@nospam_comcast.net> wrote in message
news:yf********************@comcast.com...
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

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


"Steve" <sa****@penn.com> wrote in message
news:cd******************@newsread1.news.atl.earth link.net...
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


Nov 12 '05 #3

P: n/a
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
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:yf********************@comcast.com...
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

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


"Steve" <sa****@penn.com> wrote in message
news:cd******************@newsread1.news.atl.earth link.net...
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


Nov 12 '05 #4

P: n/a
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

"PC Datasheet" <sp**@nospam.spam> wrote in message
news:SX*******************@newsread1.news.atl.eart hlink.net...
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 Krous" <m.krous@nospam_comcast.net> wrote in message
news:yf********************@comcast.com...
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

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


"Steve" <sa****@penn.com> wrote in message
news:cd******************@newsread1.news.atl.earth link.net...
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



Nov 12 '05 #5

P: n/a
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
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:3c********************@comcast.com...
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

"PC Datasheet" <sp**@nospam.spam> wrote in message
news:SX*******************@newsread1.news.atl.eart hlink.net...
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 Krous" <m.krous@nospam_comcast.net> wrote in message
news:yf********************@comcast.com...
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
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


"Steve" <sa****@penn.com> wrote in message
news:cd******************@newsread1.news.atl.earth link.net...
> 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
>
>



Nov 12 '05 #6

P: n/a
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...29&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:
<snip>
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"
'** 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 <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
"Steve" <sa****@penn.com> wrote in message
news:a0******************@newsread2.news.atl.earth link.net... 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
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:3c********************@comcast.com...
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

"PC Datasheet" <sp**@nospam.spam> wrote in message
news:SX*******************@newsread1.news.atl.eart hlink.net...
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 Krous" <m.krous@nospam_comcast.net> wrote in message
news:yf********************@comcast.com...
> 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
> 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
>
>
>
>
> "Steve" <sa****@penn.com> wrote in message
> news:cd******************@newsread1.news.atl.earth link.net...
> > 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
> >
> >
>
>



Nov 12 '05 #7

P: n/a
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!
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:-c********************@comcast.com...
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...29&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:
<snip>
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"
'** 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

<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
"Steve" <sa****@penn.com> wrote in message
news:a0******************@newsread2.news.atl.earth link.net...
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
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:3c********************@comcast.com...
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

"PC Datasheet" <sp**@nospam.spam> wrote in message
news:SX*******************@newsread1.news.atl.eart hlink.net...
> 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 Krous" <m.krous@nospam_comcast.net> wrote in message
> news:yf********************@comcast.com...
> > 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
> > 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
> >
> >
> >
> >
> > "Steve" <sa****@penn.com> wrote in message
> > news:cd******************@newsread1.news.atl.earth link.net...
> > > 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
> > >
> > >
> >
> >
>
>



Nov 12 '05 #8

P: n/a
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

"Steve" <sa****@penn.com> wrote in message
news:2N***************@newsread1.news.atl.earthlin k.net...
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!
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:-c********************@comcast.com...
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...29&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:
<snip>
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"
'** 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

<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
"Steve" <sa****@penn.com> wrote in message
news:a0******************@newsread2.news.atl.earth link.net...
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
"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:3c********************@comcast.com...
> 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
>
> "PC Datasheet" <sp**@nospam.spam> wrote in message
> news:SX*******************@newsread1.news.atl.eart hlink.net...
> > 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 Krous" <m.krous@nospam_comcast.net> wrote in message
> > news:yf********************@comcast.com...
> > > 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
> > > 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
> > >
> > >
> > >
> > >
> > > "Steve" <sa****@penn.com> wrote in message
> > > news:cd******************@newsread1.news.atl.earth link.net...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.