Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Red face Unanswered: form subform navigation

    Hi I have a main form and a subform and I use the following afterinsert event to fill the form this works well. I use the Me.AllowAdditions = False to limit user to only add the one record. When I use the tab control to cycle through to the subform which is on a page control it takes me back to the subform on the first record not the record I have just created. Any idea’s

    Thanks Bob

    Code

    Private Sub Form_AfterInsert()

    Dim db As Database
    Dim RecShiftMain As Recordset
    Dim RecShiftDetail As Recordset
    Dim RecShiftFill As Recordset
    Dim lngMainKey As Long

    Set db = CurrentDb

    'Copy the parent record and remember its Key
    Set RecShiftMain = db.OpenRecordset("tbl_ShiftMain", dbOpenDynaset)
    lngMainKey = Shift_ID


    Set RecShiftDetail = db.OpenRecordset("tbl_ShiftDetail", dbOpenDynaset)
    Set RecShiftFill = db.OpenRecordset("Qry_ShiftDetailFill", dbOpenDynaset)

    Do While Not RecShiftFill.EOF

    RecShiftDetail.AddNew
    RecShiftDetail!Shift_ID = lngMainKey 'Set to Parent Key
    RecShiftDetail!Catergory = RecShiftFill!Catergory
    RecShiftDetail!Item = RecShiftFill!Item
    RecShiftDetail!Measure = RecShiftFill!Measure
    RecShiftDetail!Sale_Unit = RecShiftFill!Sale_Unit
    RecShiftDetail!Price = RecShiftFill!Price
    RecShiftDetail!TaxIn = RecShiftFill!TaxIn

    RecShiftDetail.Update
    RecShiftFill.MoveNext

    Loop

    Me.Requery
    RecShiftFill.Close
    RecShiftMain.Close
    RecShiftDetail.Close

    Me.AllowAdditions = False

    'Me.frm_ShiftBarLiquorSubform.SetFocus

    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To be honest, I'm having a hard time understanding what you're doing, here, but the Command

    Me.Requery

    will always take you back to the beginning of the Recordset, unless you do something, just prior to issuing the Command, to bookmark the Current Record. Here's a quick tutorial on doing this:

    To Requery a Record Source and then return to the Current Record:


    Where [UniqueField] is a Field unique to only one Record:

    Where [UniqueField] is Text
    Code:
    Dim UF_Rec as String
       
       UF_Rec = Me!UniqueField
       Me.Requery
       Me.Recordset.FindFirst "[UniqueField] = '" & UF_Rec & "'"
    Where [UniqueField]is Numeric
    Code:
    Dim UF_Rec as (Fill in number type here)
       
       UF_Rec = Me!UniqueField
       Me.Requery
       Me.Recordset.FindFirst "[UniqueField] = " & UF_Rec


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2012
    Posts
    56

    Talking Problem solved

    Hi it worked just great thanks for all your help.

    Cheers Bob

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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