473,898 Members | 2,941 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error Message After Delete

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.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID

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
8 4077
Steve, I dont know that I have enough information from you but heres some
thoughts.
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID

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
Mike,

Thank you for responding!

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

If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Me!SFrmSubcontr actorDivisionLi st.SetFocus
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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.RunC ommand acCmdDeleteReco rd, an error occured,
the error handler executed and Access hung up on the DoCmd.RunComman d
acCmdDeleteReco rd 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******** ************@co mcast.com...
Steve, I dont know that I have enough information from you but heres some
thoughts.
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID

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
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******** ************@co mcast.com...
Steve, I dont know that I have enough information from you but heres some
thoughts.
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID

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
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.RunComman d acCmdDeleteReco rd. 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.sp am> wrote in message
news:SX******** ***********@new sread1.news.atl .earthlink.net. ..
Mike,

Thank you for responding!

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

If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Me!SFrmSubcontr actorDivisionLi st.SetFocus
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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.RunC ommand acCmdDeleteReco rd, an error occured, the error handler executed and Access hung up on the DoCmd.RunComman d
acCmdDeleteReco rd 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******** ************@co mcast.com...
Steve, I dont know that I have enough information from you but heres some thoughts.
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID

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
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.RunComman d acCmdDeleteReco rd, 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******** ************@co mcast.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.RunComman d acCmdDeleteReco rd. 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.sp am> wrote in message
news:SX******** ***********@new sread1.news.atl .earthlink.net. ..
Mike,

Thank you for responding!

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

If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Me!SFrmSubcontr actorDivisionLi st.SetFocus
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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.RunC ommand acCmdDeleteReco rd, an error

occured,
the error handler executed and Access hung up on the DoCmd.RunComman d
acCmdDeleteReco rd 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******** ************@co mcast.com...
Steve, I dont know that I have enough information from you but heres some thoughts.

> DoCmd.SetWarnin gs False
> DoCmd.RunComman d acCmdDeleteReco rd
> DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
> DoCmd.RunComman d acCmdDeleteReco rd
> DoCmd.SetWarnin gs True
> End If
> ExitHere:
> Me!SubName.SetF ocus
> Exit Sub
> ErrorHandler:
> If Err.Number = 3200 Then
> MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID
>
> 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
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!SFrmSubcontr actorDivisionLi st!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.FindRecor d 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.co m> wrote in message
news:a0******** **********@news read2.news.atl. earthlink.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.RunComman d acCmdDeleteReco rd, 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******** ************@co mcast.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.RunComman d acCmdDeleteReco rd. 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.sp am> wrote in message
news:SX******** ***********@new sread1.news.atl .earthlink.net. ..
Mike,

Thank you for responding!

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

If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
Me!SFrmSubcontr actorDivisionLi st.SetFocus
DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.SetWarnin gs True
End If
ExitHere:
Me!SubName.SetF ocus
Exit Sub
ErrorHandler:
If Err.Number = 3200 Then
MsgBox Me!SFrmSubcontr actorDivisionLi st!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.RunC ommand acCmdDeleteReco rd, an error

occured,
the error handler executed and Access hung up on the DoCmd.RunComman d
acCmdDeleteReco rd 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******** ************@co mcast.com...
> Steve, I dont know that I have enough information from you but heres

some
> thoughts.
>
> > DoCmd.SetWarnin gs False
> > DoCmd.RunComman d acCmdDeleteReco rd
> > DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
> news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
> > DoCmd.RunComman d acCmdDeleteReco rd
> > DoCmd.SetWarnin gs True
> > End If
> > ExitHere:
> > Me!SubName.SetF ocus
> > Exit Sub
> > ErrorHandler:
> > If Err.Number = 3200 Then
> > MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID
> >
> > 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
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!SFrmSubcontr actorDivisionLi st!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.FindRecor d 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.co m> wrote in message
news:a0******** **********@news read2.news.atl. earthlink.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.RunComman d acCmdDeleteReco rd,

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******** ************@co mcast.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.RunComman d acCmdDeleteReco rd. 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.sp am> wrote in message
news:SX******** ***********@new sread1.news.atl .earthlink.net. ..
> Mike,
>
> Thank you for responding!
>
> First, Here is a more complete sample of the code:
>
> If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
> Me!SFrmSubcontr actorDivisionLi st.SetFocus
> DoCmd.SetWarnin gs False
> DoCmd.RunComman d acCmdDeleteReco rd
> DoCmd.SetWarnin gs True
> End If
> ExitHere:
> Me!SubName.SetF ocus
> Exit Sub
> ErrorHandler:
> If Err.Number = 3200 Then
> MsgBox Me!SFrmSubcontr actorDivisionLi st!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.RunC ommand acCmdDeleteReco rd, an error
occured,
> the error handler executed and Access hung up on the DoCmd.RunComman d
> acCmdDeleteReco rd 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******** ************@co mcast.com...
> > Steve, I dont know that I have enough information from you but heres
some
> > thoughts.
> >
> > > DoCmd.SetWarnin gs False
> > > DoCmd.RunComman d acCmdDeleteReco rd
> > > DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
> > news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
> > > DoCmd.RunComman d acCmdDeleteReco rd
> > > DoCmd.SetWarnin gs True
> > > End If
> > > ExitHere:
> > > Me!SubName.SetF ocus
> > > Exit Sub
> > > ErrorHandler:
> > > If Err.Number = 3200 Then
> > > MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID
> > >
> > > 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
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.FindRecor d 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.FindRecor d 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.co m> wrote in message
news:2N******** *******@newsrea d1.news.atl.ear thlink.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!SFrmSubcontr actorDivisionLi st!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.FindRecor d 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.co m> wrote in message
news:a0******** **********@news read2.news.atl. earthlink.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.RunComman d
acCmdDeleteReco rd, 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******** ************@co mcast.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.RunComman d acCmdDeleteReco rd. 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.sp am> wrote in message
> news:SX******** ***********@new sread1.news.atl .earthlink.net. ..
> > Mike,
> >
> > Thank you for responding!
> >
> > First, Here is a more complete sample of the code:
> >
> > If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
> > Me!SFrmSubcontr actorDivisionLi st.SetFocus
> > DoCmd.SetWarnin gs False
> > DoCmd.RunComman d acCmdDeleteReco rd
> > DoCmd.SetWarnin gs True
> > End If
> > ExitHere:
> > Me!SubName.SetF ocus
> > Exit Sub
> > ErrorHandler:
> > If Err.Number = 3200 Then
> > MsgBox Me!SFrmSubcontr actorDivisionLi st!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.RunC ommand acCmdDeleteReco rd, an
error > occured,
> > the error handler executed and Access hung up on the DoCmd.RunComman d > > acCmdDeleteReco rd 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******** ************@co mcast.com...
> > > Steve, I dont know that I have enough information from you but heres > some
> > > thoughts.
> > >
> > > > DoCmd.SetWarnin gs False
> > > > DoCmd.RunComman d acCmdDeleteReco rd
> > > > DoCmd.SetWarnin gs 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.SetWarni ngs 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.co m> wrote in message
> > > news:cd******** **********@news read1.news.atl. earthlink.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.SetWarnin gs False
> > > > DoCmd.RunComman d acCmdDeleteReco rd
> > > > DoCmd.SetWarnin gs True
> > > > End If
> > > > ExitHere:
> > > > Me!SubName.SetF ocus
> > > > Exit Sub
> > > > ErrorHandler:
> > > > If Err.Number = 3200 Then
> > > > MsgBox Me!SFrmSubcontr actorDivisionLi st!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!Subco ntractorDivIDNu m = Me!Subcontracto rDivID
> > > >
> > > > 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2936
by: josh dismukes | last post by:
/// here is the code i'm getting a parse error on the last line of the code which /// is </html> any help will be much appreciated. <?php session_start ();
4
2955
by: Mark | last post by:
Not sure this is the right place for this questions, but here goes: I get an error message when deleting an table from a Access database. The code is as follows and the error message is after it. ***************** Start Code ******************** <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.Oledb" %>
5
744
by: Bob | last post by:
Hi Everybody I hope you can help. 2 related questions. 1. I am looking for a way to replace the confusing message box that comes up when a user trys to open a form without putting data in it. ie ..Syntax Error(Missing Operator) In queryexpression '=" & Me! DoCmd.OpenForm stDocName, , , stLinkCriteria
6
8486
by: Squirrel | last post by:
I have a command button on a subform to delete a record. The only statement in the subroutine is: DoCmd.RunCommand acCmdDeleteRecord The subform's recordsource is "select * from tblVisit order by VisitDt" I'm getting this error message: Errno is 2465. Err.description is "Can't find field '|' referred to in your expression"
0
2136
by: Roman | last post by:
I'm trying to create the form which would allow data entry to the Client table, as well as modification and deletion of existing data rows. For some reason the DataGrid part of functionality stops working when I include data entry fields to the form: I click on Delete or Edit inside of DataGrid and get this error: "Error: Object doesn't support this property or method" If I remove data entry fields from the form - DataGrid allows to...
8
9486
by: Razak | last post by:
Hi, I have a class which basically do Impersonation in my web application. From MS KB sample:- ++++++++++++++++++++code starts Dim impersonationContext As System.Security.Principal.WindowsImpersonationContext Dim currentWindowsIdentity As System.Security.Principal.WindowsIdentity
2
3354
by: hansiman | last post by:
I'm wondering how best to present exception messages to a user. In a try catch I catch an OleDb.OleDbException. The error could be a COLUMN REFERENCE constraint error (by a deletion attempt). The normal user would not know what to make of the standard error message (exOleDb.Message) and I'm not sure how best to use the the exOleDb.ErrorCode (-2147217873)? Is best practise to present the user to a general message like "An SQL
12
2588
by: Premal | last post by:
Hi, I tried to make delete operator private for my class. Strangely it is giving me error if I compile that code in VC++.NET. But it compiles successfully on VC++6.o. Can anybody give me inputs about it. I wanted that on my class delete should not work. Object pointer should be deleted using my function only which is taking care of reference count for particular class. Thanx in advance for your inputs.
5
3194
by: prakashwadhwani | last post by:
The Delete Event/Proc & "Save_Fields_In_Form_Header" Event/Proc in my form were working perfectly. However, after I added a call to the "Save_Fields_In_Form_Header" Event/Proc in the "Current" Event of my form, and I now try to Delete a record, I get ... Run-Time Error 3218 - Could not Update; Currently Locked. My Access application then effectively freezes forcing me to shut Access down & re-start. The record DOES get deleted...
0
9993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11265
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10949
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10484
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6077
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4296
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3306
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.