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" <santus@penn.com> wrote in message
news:2Nftb.962$Rk5.500@newsread1.news.atl.earthlin k.net...[color=blue]
> Mike,
>
> That eliminated the "Operation Not Supported In Transactions" error[/color]
message.[color=blue]
> Thank you very much for the help!!
>
> The only thing now is FindRecord. When Requery is executed the recordset[/color]
jumps[color=blue]
> to the first record as expected and then the FindRecord code DOES NOT[/color]
bring the[color=blue]
> 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[/color]
the[color=blue]
> bookmark of a specified record but then it takes additional code to move[/color]
the[color=blue]
> 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:-cednTg2z_PKeimi4p2dnA@comcast.com...[color=green]
> > 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[/color][/color]
Knowledgebase[color=blue][color=green]
> > 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:
> >[/color][/color]
http://support.microsoft.com/default...29&Product=acc[color=blue][color=green]
> > )
> >
> > 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>[color=darkred]
> > >If Err.Number = 3200 Then
> > > MsgBox Me!SFrmSubcontractorDivisionList!DivisionName & vbCrLf _
> > > & "Can Not Be Deleted From The Database Because There Are" & vbCrLf[/color][/color][/color]
_[color=blue][color=green][color=darkred]
> > > & "One Or More Locations Recorded For The Division.", , "Division[/color][/color][/color]
Can[color=blue][color=green]
> > Not Be Deleted"
> > '** NEW CODE **
> > lngID = txtid 'get a unique value from the current record,[/color][/color]
(you'd[color=blue][color=green]
> > 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[/color][/color]
issued[color=blue][color=green]
> > a requery
> > DoCmd.FindRecord lngID, acEntire, , acSearchAll, True, acCurrent,[/color][/color]
True[color=blue][color=green]
> > '** End New Code **[color=darkred]
> > >Else[/color]
> > <snip>
> >
> > The new code will have to be adapted to your controls but I think you[/color][/color]
get[color=blue][color=green]
> > 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[/color][/color]
broken[color=blue][color=green]
> > link.
> >
> > HTH
> >
> > Mike Krous
> >
> >
> > "Steve" <santus@penn.com> wrote in message
> > news:a0Psb.27414$9M3.3481@newsread2.news.atl.earth link.net...[color=darkred]
> > > Mike,
> > >
> > > Added DoCmd.Requery as you suggested - still get the error. I don't[/color][/color][/color]
think[color=blue][color=green]
> > it[color=darkred]
> > > ever gets to the Requery. It gets to DoCmd.RunCommand[/color][/color][/color]
acCmdDeleteRecord,[color=blue][color=green]
> > an[color=darkred]
> > > error occurs and execution jumps to the error handler.
> > >
> > > There is a main form with a tab control. On one page of the tab[/color][/color][/color]
control is[color=blue][color=green]
> > a[color=darkred]
> > > subform(first subform) of the main form and another subform(second[/color]
> > subform) of[color=darkred]
> > > the first subform. The second subform is external of the first subform[/color][/color][/color]
and[color=blue][color=green]
> > is[color=darkred]
> > > synchronized with the first subform through a textbox.
> > >
> > > Steve
> > >
> > >
> > > "Mike Krous" <m.krous@nospam_comcast.net> wrote in message
> > > news:3cCdnb-jTZ5kEy6i4p2dnA@comcast.com...
> > > > Your right you did say in the sub-form, sorry for that! However I do[/color]
> > believe[color=darkred]
> > > > I am a little unclear at the moment, how many subforms do you have[/color][/color][/color]
on[color=blue][color=green]
> > this[color=darkred]
> > > > form and are they subforms of subforms? I was originally thinking[/color][/color][/color]
you[color=blue][color=green]
> > had[color=darkred]
> > > > one form and one subform...just curious?
> > > >
> > > > also im not sure, but you could try a DoCmd.Requery immediatly after[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > DoCmd.RunCommand acCmdDeleteRecord. The idea here is to maybe[/color][/color][/color]
allow[color=blue][color=green]
> > access[color=darkred]
> > > > to refresh itself before you attempt to move. Although I must say[/color][/color][/color]
FWIW,[color=blue][color=green]
> > I[color=darkred]
> > > > did a search on google and no-one showed a requery in there[/color][/color][/color]
examples,[color=blue][color=green]
> > but[color=darkred]
> > > > they also didnt have subforms in the examples either...
> > > >
> > > > HTH
> > > >
> > > > Mike Krous
> > > >
> > > > "PC Datasheet" <spam@nospam.spam> wrote in message
> > > > news:SXLsb.24439$Oo4.20223@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[/color][/color][/color]
_[color=blue][color=green][color=darkred]
> > > > > & "Can Not Be Deleted From The Database Because There Are" &[/color]
> > vbCrLf[color=darkred]
> > > > _
> > > > > & "One Or More Locations Recorded For The Division.", ,[/color]
> > "Division[color=darkred]
> > > > 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[/color][/color][/color]
new[color=blue][color=green][color=darkred]
> > > > record in
> > > > > the first subform not the main form. However, I did try your quick[/color]
> > test[color=darkred]
> > > > and
> > > > > found that the problem occurs when I try to move to a new record[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > main
> > > > > form, first subform or second subform. This seems to say that[/color][/color][/color]
Access[color=blue][color=green]
> > tried[color=darkred]
> > > > to
> > > > > execute this line of code,DoCmd.RunCommand acCmdDeleteRecord, an[/color][/color][/color]
error[color=blue][color=green][color=darkred]
> > > > occured,
> > > > > the error handler executed and Access hung up on the[/color][/color][/color]
DoCmd.RunCommand[color=blue][color=green][color=darkred]
> > > > > acCmdDeleteRecord code???
> > > > >
> > > > > I did try exactly as you said for a quick tes, I clicked on a[/color][/color][/color]
field in[color=blue][color=green]
> > the[color=darkred]
> > > > main
> > > > > form and then tried to move to a new record. The same error[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > > appeared.
> > > > >
> > > > > Steve
> > > > >
> > > > >
> > > > >
> > > > > "Mike Krous" <m.krous@nospam_comcast.net> wrote in message
> > > > > news:yf6dnfuAX6DUGS6i4p2dnA@comcast.com...
> > > > > > Steve, I dont know that I have enough information from you but[/color][/color][/color]
heres[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
see[color=blue][color=green]
> > an[color=darkred]
> > > > If,
> > > > > > so if you are able to, I suggest you post the code above the[/color][/color][/color]
snipet[color=blue][color=green]
> > you[color=darkred]
> > > > > > already posted (above ->DoCmd.SetWarnings False...).
> > > > > >
> > > > > >
> > > > > > Also, just for a quick test: After deleting a record on the[/color]
> > sub-form,[color=darkred]
> > > > > > before you attempt to move to another record on the main form,[/color][/color][/color]
click[color=blue][color=green]
> > on[color=darkred]
> > > > a
> > > > > > field of the main form (make sure the main form has the focus),[/color][/color][/color]
then[color=blue][color=green][color=darkred]
> > > > attempt
> > > > > > to move to the next record on the main form.
> > > > > >
> > > > > > Let us know
> > > > > >
> > > > > > Mike Krous
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Steve" <santus@penn.com> wrote in message
> > > > > > news:cdBsb.23890$Oo4.3749@newsread1.news.atl.earth link.net...
> > > > > > > I have several pairs of synchronized subforms in an[/color][/color][/color]
application. I[color=blue][color=green][color=darkred]
> > > > have a
> > > > > > Delete
> > > > > > > button for each pair that uses the following code or similar[/color][/color][/color]
to[color=blue][color=green]
> > delete[color=darkred]
> > > > 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 &[/color][/color][/color]
vbCrLf[color=blue][color=green]
> > _[color=darkred]
> > > > > > > & "Can Not Be Deleted From The Database Because There[/color][/color][/color]
Are" &[color=blue][color=green][color=darkred]
> > > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > message
> > > > > > for
> > > > > > > Err 3200 and all seems to work fine. However, if I then try to[/color]
> > move to[color=darkred]
> > > > a
> > > > > > > different record in the first subform, I get the error message
> > > > "Operation
> > > > > > Not
> > > > > > > Supported In Transactions". There is code in the OnCurrent[/color][/color][/color]
event[color=blue][color=green]
> > of[color=darkred]
> > > > the
> > > > > > first
> > > > > > > subform:
> > > > > > >
> > > > > > > Me.Parent!SubcontractorDivIDNum = Me!SubcontractorDivID
> > > > > > >
> > > > > > > that synchronozes the subforms and it works error free when[/color]
> > navigating[color=darkred]
> > > > > > through
> > > > > > > records in the first subform until I try to delete a record[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> > > > related
> > > > > > records
> > > > > > > in the second subform.
> > > > > > >
> > > > > > > Does anyone have any ideas about what is causing the error[/color]
> > message?[color=darkred]
> > > > > > >
> > > > > > > Thanks!
> > > > > > >
> > > > > > > Steve
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
