Results 1 to 10 of 10
  1. #1
    nomij is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2008
    Posts
    4

    Go To a New record in a subform of a subform

    Hi All!
    I am having the difficulty of going to a new record in my sub-sub form.



    I have created a form (Annual Cks) that has nested in it a datasheetstyle subform (Notes).

    When working on this form, I was able to create a button that used the DoCmd.GoToRecord , , acNew function. First, I set focus on the subform, and then called that "go to new record" function. It inserted a line into my subform "Notes". It worked like gravy

    But when I dropped this form (Annual Cks) into a parent Form (Generator Overview), it messed it all up

    Now when I click the button, it goes to a new record in Annual Cks, not in Notes! It still sets focus on the Notes SubSubForm, and records that data I instruct it to. But after going to a new record in Annual Cks.

    Ok, so the DoCmd.GoToRecord function is now referring by default to the Annual Cks. So, I go to specify the subform in the call arguements. I get an error saying I'm referring to the wrong data type. (I specified dataform, and named the right form name). So I try to get a macro to do it. (That way I can view the VB code generated by the macro and see if I was wrong syntactically). But when I run the macro it says that the from isn't open! (Which, it is, in subsubform mode).

    Frustratingly, when I have the curser on a line in the Notes Subsubform, I can go up to the menus accross the top and click on "add new record, Ctrl++" and it does insert a new line. In my head, if I can do it as a user... I SHOULD be able to do it with VB Code.

    The only thing I can think that it is is something to do with the scope of the code. But I find that a bit confusing.

    Does anyone have any suggestions?

    Thanks so much!
    Naomi

  2. #2
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Can you post your code? I think this may have something to do with the sequence of events in your code?

  3. #3
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hi Naomi,

    (Apologies for butting in, jgelpi16, but this has intrigued me.)

    I set up a simple test replica of what I assumed your project structure to be and then coded the following VBA behind the command button click event:

    DoCmd.GoToRecord acDataForm, Me.Child6.Form.Name, acNewRec

    where Child6 is the control that contains the subform. To my surprise I also received the run time message that the form is not open. I tried this with a datasheet subform and a standard form subform with identical results. I can interrogate the subform through VBA and read values from it. So I conclude that DoCmd cannot see/access the form when it's a subform.

    Does your subform have to be a datasheet? A characteristic (fancy word for problem) of datasheets is that although you can specify form headers and footers, Access seems to ignore them. The consequence is that you cannot place a command button on a datsheet. When I used a standard form I placed a command button in the form footer with the following VBA code behind its click event:

    DoCmd.GoToRecord , , acNewRec

    It worked as you say like gravy - where does that expression come from?

    I don't think a standard form is what you want but can you 'make do' with a continuous form (now renamed by MS as a multiple item form)? Put a command button in the form header or footer. This is a work-around; the original problem remains; you should be able to manipulate the subform from practically anywhere. As you rightly point out, if you can do it as a user, why not in code.

    Regards,

    Rod

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    The code for moving to a new record on a sub - subform (subform two levels deep) from code on the main form is to use:

    Code:
    Me.SubformControlName1Here.Form.SubformControlName2Here.Form.Recordset.AddNew
    Remembering that you have to refer to the subform CONTROLS (control on the parent form which HOUSES the subform) and not the subform itself, unless they share the exact same name.
    Last edited by boblarson; 06-16-2011 at 09:45 AM. Reason: oops, put in MoveNext when I meant AddNew

  5. #5
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hi Naomi,

    I have had some further thoughts.

    I placed the following VBA behind the command button:

    Me.Child6.Form.DataEntry = True

    This worked, even for datasheets, by placing the subform in data entry mode. Unfortunately it hides the existing records so you need to switch back when you have finished data entry.

    I then tried the following code behind the command button:

    Dim frm As Form_frmtblNote
    Set frm = Me.Child6.Form
    frm.PositionAtNewRecord

    where Form_frmtblNote is the subform's class and Child6 is the subform control as before.

    In the Form_frmtblNote module I coded:

    Public Sub PositionAtNewRecord()
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Unfortunately this does not work as it results in a new note record for a NEW Annual Cks record. What's all this then: subforms changing their parents without my permission. Shame on whoever designed this one! No seriously I can understand how the situation arises: the relationship between parent and child is ignored.

    Keep smiling,
    Rod

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by Rod View Post
    Hi Naomi,

    I have had some further thoughts.

    I placed the following VBA behind the command button:

    Me.Child6.Form.DataEntry = True

    This worked, even for datasheets, by placing the subform in data entry mode. Unfortunately it hides the existing records so you need to switch back when you have finished data entry.

    I then tried the following code behind the command button:

    Dim frm As Form_frmtblNote
    Set frm = Me.Child6.Form
    frm.PositionAtNewRecord

    where Form_frmtblNote is the subform's class and Child6 is the subform control as before.

    In the Form_frmtblNote module I coded:

    Public Sub PositionAtNewRecord()
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Unfortunately this does not work as it results in a new note record for a NEW Annual Cks record. What's all this then: subforms changing their parents without my permission. Shame on whoever designed this one! No seriously I can understand how the situation arises: the relationship between parent and child is ignored.

    Keep smiling,
    Rod
    If you use the code I gave it will work fine (as long as you refer to the subforms properly). You can also use it for things like .MoveNext, .MovePrevious, .MoveLast, .MoveFirst and such.

  7. #7
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Thanks Bob,

    Yes of course, I completely overlooked the recordset object. Well it was late last night :-)

    Rod

  8. #8
    nomij is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2008
    Posts
    4
    Oh, how I forgot how much I love record sets!!!!
    Thank you and Salamat Po to you both, gentlemen! It is so much appreciated!!!
    The golden solution turned out to be:
    me.AnnualCkNotesSubform.form.recordset.AddNew

    Thanks so very much again!
    Nomi >^,,^<

  9. #9
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Walang ano man.

  10. #10
    sneuberg is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    23

    Looking for hours

    Quote Originally Posted by boblarson View Post
    The code for moving to a new record on a sub - subform (subform two levels deep) from code on the main form is to use:

    Code:
    Me.SubformControlName1Here.Form.SubformControlName2Here.Form.Recordset.AddNew
    Remembering that you have to refer to the subform CONTROLS (control on the parent form which HOUSES the subform) and not the subform itself, unless they share the exact same name.
    This was a big help. DoCmd.GotoRecord doesn't seem to work at all for this type of situation.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-15-2011, 03:53 PM
  2. Replies: 2
    Last Post: 11-26-2010, 04:20 AM
  3. Replies: 5
    Last Post: 06-29-2010, 01:24 PM
  4. new record is subform
    By Seven in forum Forms
    Replies: 5
    Last Post: 01-10-2010, 08:24 PM
  5. Subform add new record
    By ricardo9211 in forum Forms
    Replies: 0
    Last Post: 08-26-2009, 12:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums