Results 1 to 7 of 7

select Subform record and go to record on form

  1. #1
    pcool is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3

    select Subform record and go to record on form

    I want to know if there is a way to select a record in a Subform on Form A and have it go to that record on Form B. I know you can do this using a listbox, but I am trying to get it to work with a subform.
    any ideas/examples?

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,662
    Do subform and Form B have the same data source?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    pcool is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    The subform is a query of the same data used in form B (which is based on a table). So I have a search form (form A) where you can look up information from different tables in a subform (Subform on Form A).
    I want to make it so you can click (or press a button) on a record in the subform and go to that record on Form B.

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,662
    Opening two forms to the same record can be problemmatic. That is like two people accessing the same record.

    What do you mean by 'different tables in a subform'?

    However, the code would be like:

    DoCmd.OpenForm "form name", , , "ID=" & Me.ID

    The trick is figuring out what event the code would go in. Could be the subform's DoubleClick event or a Button click event.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,118
    If you're saying both forms are already open, you could adapt this technique:

    http://www.baldyweb.com/Bookmark.htm

    by removing the OpenForm and adjusting the form references appropriately.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  6. #6
    pcool is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    Thank you for all the replies. I'm going try your suggestions, and see if I can get them to work.

  7. #7
    Phil1 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    14
    If both the main form and the subform are using the same recordset you can use the 'Move' clause.

    I found the following you could try...


    Dim lngrecordnum As Long 'Returns the current record number as LONG number.
    lngrecordnum = FrmA.
    CurrentRecord

    FrmB.Move(lngrecordnum)


    I've used the .Move clause on a DAO recordset successfully in my own project

    worth a shot. see if it works.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-06-2012, 01:58 PM
  2. Replies: 1
    Last Post: 03-15-2011, 02:53 PM
  3. Replies: 1
    Last Post: 11-30-2010, 08:05 AM
  4. Replies: 5
    Last Post: 06-29-2010, 12:24 PM
  5. Replies: 3
    Last Post: 03-22-2010, 03:30 PM

Posting Permissions

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