Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Returning to Subform records (2003 SP2)

    I'm working on a main form with four subforms and when any update takes place on any subform, I have some code to do cloning (thank you Hans!!). But I need to cursor to return to the same record that was updated before the cloning procedure. This is what I have done but it doesn't quite work - actually, it only works as far as returning to the same main form record.
    ================================================== ==============
    Private Sub Form_AfterUpdate()
    Dim rstMain As Object, rstSect As Object, rstProc As Object, rstItem As Object,
    Dim lngMainID As Long, lngSectID As Long, lngProcID As Long, lngItemID As Long

    Set rstMain = Me.Parent.RecordsetClone
    Set rstSect = Me.Parent![frmSiteSection Subform].Form.RecordsetClone
    Set rstProc = Me.Parent![frmSiteProc Subform].Form.RecordsetClone
    Set rstItem = Me.Parent![frmSiteItem Subform].Form.RecordsetClone

    Me.Parent.Painting = False
    lngMainID = Me.Parent![SiteID]
    lngSectID = Me.Parent![frmSiteSection Subform].Form.SectionId
    lngProcID = Me.Parent![frmSiteProc Subform].Form.DocSectLineID
    lngItemID = Me.Parent![frmSiteItem Subform].Form.DocSubItemId

    If Me.Parent![chkAutoClone] Then
    CloneOneDocument Me.Parent![frmSiteDocument Subform].Form.DocumentNo, lngSiteID
    Else
    End If

    Me.Parent.Recalc
    DoCmd.ShowAllRecords
    rstMain.FindFirst "[SiteID] = " & lngSDID
    If Not rstMain.NoMatch Then
    Me.Parent.Bookmark = rstMain.Bookmark
    End If

    ' find Section
    rstSect.FindFirst "[SectionID] = " & lngSectID
    If Not rstSect.NoMatch Then
    Me.Parent![frmSiteSection Subform].Form.Bookmark = rstSect.Bookmark
    Me.Parent![frmSiteSection Subform].SetFocus
    DoCmd.GoToControl "SectionCode"
    End If

    ' find Procedure
    rstProc.FindFirst "[ProcID] = " & lngProcID
    If Not rstProc.NoMatch Then
    Me.Parent![frmSiteProc Subform].Form.Bookmark = rstProc.Bookmark
    Me.Parent![frmSiteProc Subform].SetFocus
    DoCmd.GoToControl "cboFrequency"
    End If

    ' find Item
    rstItem.FindFirst "[ItemId] = " & lngItemID
    If Not rstItem.NoMatch Then
    Me.Parent![frmSiteItem Subform].Form.Bookmark = rstItem.Bookmark
    Me.Parent![frmSiteItem Subform].SetFocus
    DoCmd.GoToControl "ItemId"
    End If
    Me.Parent.Painting = True

    rstMain.Close
    rstSect.Close
    rstProc.Close
    rstItem.Close
    Set rstMain = Nothing
    Set rstSect = Nothing
    Set rstProc = Nothing
    Set rstItem = Nothing
    End Sub
    ================================================== ==============

    Please, what is wrong with this code?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Returning to Subform records (2003 SP2)

    I'm not sure if it will help, but I'd move the three lines that set the subform recordsetclones

    lngSectID = Me.Parent![frmSiteSection Subform].Form.SectionId
    lngProcID = Me.Parent![frmSiteProc Subform].Form.DocSectLineID
    lngItemID = Me.Parent![frmSiteItem Subform].Form.DocSubItemId

    to below

    If Not rstMain.NoMatch Then
    Me.Parent.Bookmark = rstMain.Bookmark
    End If

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning to Subform records (2003 SP2)

    Sorry Hans. I ought to take more notice of my quote from Thoreau. I had left some vital lines out of the code - oops! Thanks for your help anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •