Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Requery form/sub-form after new record entered into subform

    Hi there,

    I have a study database where we are recruiting study participants, and using Access to manage all their details. I have a tabbed frm, where there is the main form (frm_all) and 5 tabs, one of which contains a sub-form (frm_eoisub). On this particular tab is where the details of new participants are entered. After entering the details of the new person I need to save the record and then requery the whole form but leaving focus where it is. The main form then populates with the participants Study Number, date of birth and name.

    I have been successful in using a simple macro (Save, Requery), however this always moves focus back to the first record, which is most annoying.

    I've tried numerous other codes from other forums, but have hasd no success. The closest I got was:

    Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As
    Long
    Set frmMain = Forms![frm_all]
    Set sfrm = frmMain![frm_eoisub].Form
    hldMainID = frmMain![StudyNo]
    hldsfrmID = sfrm![StudyNo]

    frmMain.Requery
    frmMain.Recordset.findFirst "[StudyNo] = " & hldMainID
    sfrm.Recordset.FindFirst "[StudyNo] = " & hldsfrmID


    This code will save and keep focus for existing records, but doesn't work for new records as the Main form doesn't yet have a Study Number for the participant.

    Any help would be greatly appreciated!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I admit to be ignorant of tabbed forms, but why don't you try a bookmark? Simply DIM a variable, say bMark as Variant, and when you're ready to requery, add
    Code:
    bMark = Me.Bookmark
    After requerying, add
    Code:
    Me.Bookmark = bMark
    to return to the correct record.

    Sam

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by gophbeav View Post
    Hi there,
    ...After entering the details of the new person I need to save the record and then requery the whole form but leaving focus where it is. The main form then populates with the participants Study Number, date of birth and name...

    ...code will save and keep focus for existing records, but doesn't work for new records as the Main form doesn't yet have a Study Number for the participant.
    Two problems here:

    First off, you appear to be entering a Child Record before the Parent Record exists.

    Secondly, you're apparently committing multiple Fields to multiple Tables, a huge no no in Access!

    Data for an individual, such as Study Number, date of birth and name, would normally be stored in the Table that the Main Form is based on, and in any dependent Tables only the unique identifier, which I'm assuming in this case is Study Number, would be stored.

    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

Tags for this Thread

Posting Permissions

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