Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    10

    Unanswered: Select subform record to display in main form

    I have a main form that is used to enter various details about a member's contributions. The main form contains a subform that displays all of the members prior contributions.

    I would like to have Access 2007 display in the main form the details of the record that I have selected in the subform.

    Aside from this point, the subform works fine in displaying all of the member's records.

    Thanks.

    Bill

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    top form has members details
    sub form has details of members contributions

    displaying the details of a specific contribution on the top form makes no sense to me. it breaks the pont of having a parent (top) form and a child (sub form)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1) Let's suppose that there is a column that uniquely identifies a record in the data source of both forms (primary key or indexed with UNIQUE constraint). In my example this column is: SysCounter.

    2) In the main (parent) form:
    Code:
    Public Function GotoRecord(RecordID As Long)
    
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        Set rst = Me.RecordsetClone
        strCriteria = "SysCounter = " & RecordID
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then
            Me.Bookmark = rst.Bookmark
        End If
        Set rst = Nothing
        
    End Function
    3) In the subform:
    Code:
    Private Sub Form_Current()
    
        Call Me.Parent.GotoRecord(Me.SysCounter.Value)
        
    End Sub
    Voilà!

    Have a nice day!

  4. #4
    Join Date
    Aug 2009
    Posts
    10
    many thanks for your help.

  5. #5
    Join Date
    Aug 2012
    Posts
    2

    What if the Primary Key is Text (eg A001)

    Quote Originally Posted by Sinndho View Post
    1) Let's suppose that there is a column that uniquely identifies a record in the data source of both forms (primary key or indexed with UNIQUE constraint). In my example this column is: SysCounter.

    2) In the main (parent) form:
    Code:
    Public Function GotoRecord(RecordID As Long)
    
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        Set rst = Me.RecordsetClone
        strCriteria = "SysCounter = " & RecordID
        rst.FindFirst strCriteria
        If rst.NoMatch = False Then
            Me.Bookmark = rst.Bookmark
        End If
        Set rst = Nothing
        
    End Function
    3) In the subform:
    Code:
    Private Sub Form_Current()
    
        Call Me.Parent.GotoRecord(Me.SysCounter.Value)
        
    End Sub
    Voilà!

    Have a nice day!

    Hi Sinndho, your solution works perfectly fine for me but I have problem when another table has a primary, index key of TEXT format. it doesn't show the records on the main form.

    I have tried using this solution: strCriteria = "FirmaID = ' " & RecordID & "'"

    but when i click on the records in subform, it doesn't show

    FirmaID is the index key (Eg: A001)

    Please any solution to it would be appreciated.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Did you remember to change the data type of the argument in the function?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Aug 2012
    Posts
    2
    Yeah I change it. Below is my code:

    On my main Form:

    Public Function GotoRecord(RecordID As String)

    Dim rst As DAO.Recordset
    Dim strCriteria As String
    Set rst = Me.RecordsetClone
    strCriteria = "FirmaID = ' " & RecordID & "'"
    rst.FindFirst strCriteria
    If rst.NoMatch = False Then
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing
    End Function


    On my subform:

    Private Sub Form_Current()
    Call Me.Parent.GotoRecord(Me.FirmaID.Value)
    End Sub

Posting Permissions

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